﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ANZ_AIM_BLL;
using ANZ_AIM_FRAMEWORK;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_GUI.Utils;
using DevComponents.Editors.DateTimeAdv;
using ButtonX = DevComponents.DotNetBar.ButtonX;
using CheckBoxX = DevComponents.DotNetBar.Controls.CheckBoxX;
using DataGridViewX = DevComponents.DotNetBar.Controls.DataGridViewX;
using System.Globalization;
using System.IO;
using log4net;

namespace ANZ_AIM_GUI
{
    public partial class frmReport : Form
    {
        //log4net
        private static readonly ILog logger = LogManager.GetLogger(typeof(frmReport));

        //Dynamic Control
        TextBox txtFileName;
        TextBox txtSalesCode;
        ComboBox cbbStatus;
        ComboBox cbbMOB;
        ComboBox cbbProduct;
        DateTimeInput dtiFromMonth;
        DateTimeInput dtiToMonth;
        CheckBoxX cbViewAll;
        ComboBox cbbField;
        DataGridViewX dgvFile;
        DataGridViewX dgvData;

        #region GLOBAL VARIABLES

        Dictionary<string, string> Mapping_SaleCode_PersonalCode;
        Dictionary<string, string> Mapping_SaleCode_ProfileID;
        private int optMode = 0;
        private int tabNumber = 0;
        private string currentDateOfFile = DateTime.Now.Year.ToString() + " M" + DateTime.Now.Month.ToString();
        protected AppParam appParam = AppParam.Instance;

        //List of field added to GridView
        List<int> colIndex = new List<int>();

        //alert disciplinarysaleSQMRawDataList
        public delegate void SetUpdate(string Status);

        // khai báo 1 kieu hàm delegate
        public SetUpdate DISUpdateStatus;

        //Declare SaleInfoBLL to get PersonalCode
        SaleInfoBLL saleInfoBll = new SaleInfoBLL();

        #endregion

        #region CREATE LISTS OF REPORT DATA

        public List<SalePIP> salePIPList = new List<SalePIP>();
        public List<SaleSQM> saleSQMList = new List<SaleSQM>();
        public List<SaleSQMRawData> saleSQMRawDataList = new List<SaleSQMRawData>();
        public List<SalePayment> salePaymentList = new List<SalePayment>();
        public List<SaleDIS> saleDISList = new List<SaleDIS>();
        public List<AgentCards> agentCardsList = new List<AgentCards>();
        public List<AgentCardsActivationRate> agentCardActvtrateList = new List<AgentCardsActivationRate>();
        public List<AgentFieldSales> agentFieldSalesList = new List<AgentFieldSales>();
        public List<AgentLOC> agentLOCList = new List<AgentLOC>();
        public List<AgentPersonalLoan> agentPersonalLoanList = new List<AgentPersonalLoan>();
        public List<AgentSuppCard> agentSuppCardList = new List<AgentSuppCard>();
        public List<TeamLeaderCards> teamLeaderCardsList = new List<TeamLeaderCards>();
        public List<TeamLeaderFieldSales> teamLeaderFieldSalesList = new List<TeamLeaderFieldSales>();
        public List<TeamLeaderPL> teamLeaderPLList = new List<TeamLeaderPL>();
        public List<TeamLeaderPortfolio> teamLeaderPortfolioList = new List<TeamLeaderPortfolio>();
        public List<MonthlyAgentList> monthAgentList = new List<MonthlyAgentList>();
        public List<SaleAttendance> saleAttendanceList = new List<SaleAttendance>();

        #endregion

        #region Custom Report var
        Dictionary<string, Dictionary<string, string>> selectFieldReport = new Dictionary<string, Dictionary<string, string>>();
        Dictionary<string, string> selectFieldReport4Checking = new Dictionary<string, string>();
        Dictionary<string, string> CustomReportTableAndAliasMap = new Dictionary<string, string> 
        { 
            {"SalePip", "PIP "}
            , {"SaleSQM", "SQM "}
            , {"SaleDIS", "DIS "}
            , {"SalePayment", "PAYMENT "}
            , {"AgentCardsActivationRate", "AG CARD RATE "}
            , {"AgentCards", "AG CARD "}
            , {"AgentPersonalLoan", "AG LOAN "}
            , {"AgentLOC", "AG LOC "}
            , {"AgentSuppCard", "AG SUP "}
            , {"AgentFieldSales", "AG SALES "}
            , {"TeamLeaderCards", "LEADER CARD "}
            , {"TeamLeaderFieldSales", "LEADER SALES "}
            , {"TeamLeaderPL", "LEADER PL"}
            , {"TeamLeaderPortfolio", "LEADER PFLIO "}
            , {"MonthlyAgentList", "AG LIST "}
        };
        #endregion

        public frmReport()
        {
            InitializeComponent();
            LoadComponent();
            SaleCodeMapping();
            tabPIP.PerformClick();

            //Clear selected field on Custom Report
            selectFieldReport.Clear();
        }

        public frmReport(int TabIndex)
            : this()
        {
            tabReport.SelectedTabIndex = TabIndex;
            optMode = 1;
            frmSplash.ShowSplashScreen();
            if (TabIndex == 2)
            {
                LoadDataControlByTab(2);
                LoadFileList(Constant.TYPE_DIS);
                LoadSaleData();
            }
            frmSplash.CloseForm();
        }

        //Set Permission for Control
        private void LoadComponent()
        {
            this.btnPIPBrowseFile.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnPIPUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtPIPFileName.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);

            this.btnSQMBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnSQMUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtSQMFileName.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);

            this.btnDISBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnDISUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtDISFileName.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            btnDISChangeStatus.Enabled = appParam.Role.Contains(User.ROLE_APPROVER);

            this.btnPaymentBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnPaymentUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtPaymentFile.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);

            this.btnSalesProdtvtBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnSaleProdtvtUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtSalesProdtvtFileName.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);

            this.btnMonthlyAgentListBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnMonthlyAgentListUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtFileNameMonthlyAgentList.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);

            this.btnMonthlyAgentListBrowse.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.btnMonthlyAgentListUpload.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
            this.txtFileNameMonthlyAgentList.Enabled = appParam.Role.Contains(User.ROLE_EXECUTER);
        }

        #region COMMON METHODS

        /// Bind dynamic controls in each tab to common variables
        /// <param name="tabNumber">
        /// 0. PIP
        /// 1. SQM
        /// 2. Disciplinary Tracker
        /// 3. Payment
        /// 4. Sales Productivity
        /// 5. Sales Productivity - Agent
        /// 6. Sales Productivity - Team Leader
        /// 7. Monthly Agent List
        /// 8. Custom Report
        /// </param>
        private void LoadDataControlByTab(int tabNumber)
        {
            try
            {
                this.tabNumber = tabNumber;
                colIndex.Clear();
                if (tabNumber == 0)
                {
                    txtFileName = txtPIPFileName;
                    txtSalesCode = txtPIPSaleCode;
                    cbbStatus = cbPIPStatus;
                    dtiFromMonth = dtPIPFromMonth;
                    dtiToMonth = dtPIPToMonth;
                    cbViewAll = cbPIPViewAll;
                    cbbField = cbPIPField;
                    dgvFile = dgvPIPFile;
                    dgvData = dgvPIPData;
                }
                else if (tabNumber == 1)
                {
                    txtFileName = txtSQMFileName;
                    txtSalesCode = txtSQMSaleCode;
                    dtiFromMonth = dtSQMFromMonth;
                    dtiToMonth = dtSQMToMonth;
                    cbViewAll = cbSQMViewAll;
                    cbbField = cbSQMField;
                    dgvFile = dgvSQMFile;
                    dgvData = dgvSQMData;
                }
                else if (tabNumber == 2)
                {
                    txtFileName = txtDISFileName;
                    txtSalesCode = txtDISSaleCode;
                    cbbStatus = cbbDISStatus;
                    dtiFromMonth = dtDISFromMonth;
                    dtiToMonth = dtDISToMonth;
                    cbViewAll = cbDISViewAll;
                    cbbField = cbDISField;
                    dgvFile = dgvDISFile;
                    dgvData = dgvDISData;
                }
                else if (tabNumber == 3)
                {
                    txtFileName = txtPaymentFile;
                    txtSalesCode = txtSaleCodePayment;
                    dtiFromMonth = dtiFromMonthPayment;
                    dtiToMonth = dtiToMonthPayment;
                    cbViewAll = cbViewAllPayment;
                    cbbField = cbbFieldPayment;
                    dgvFile = dgvFileNamePayment;
                    dgvData = dgvDataPayment;
                }
                else if (tabNumber == 4)
                {
                    txtFileName = txtSalesProdtvtFileName;
                    dgvFile = dgvFileSaleProdtvt;
                }
                else if (tabNumber == 5)
                {
                    txtSalesCode = txtSaleCodeAgent;
                    cbbMOB = cbbMOBAgent;
                    cbbProduct = cbbProductAgent;
                    dtiFromMonth = dtiFromMonthAgent;
                    dtiToMonth = dtiToMonthAgent;
                    cbViewAll = cbViewAllAgent;
                    cbbField = cbbFieldAgent;
                    dgvData = dgvDataAgent;
                }
                else if (tabNumber == 6)
                {
                    txtSalesCode = txtSaleCodeTeamLd;
                    dtiFromMonth = dtiFromMonthTeamLeader;
                    dtiToMonth = dtiToMonthTeamLeader;
                    cbViewAll = cbViewAllTeamLd;
                    cbbField = cbbFieldTeamLd;
                    dgvData = dgvDataTeamLeader;
                }
                else if (tabNumber == 7)
                {
                    txtFileName = txtFileNameMonthlyAgentList;
                    txtSalesCode = txtSaleCodeMonthlyAgentList;
                    cbbStatus = cbbStatusMonthlyAgentList;
                    dtiFromMonth = dtiFromMonthMonthlyAgentList;
                    dtiToMonth = dtiToMonthMonthlyAgentList;
                    cbViewAll = cbViewAllMonthlyAgentList;
                    cbbField = cbbFieldMonthlyAgentList;
                    dgvFile = dgvFileMonthlyAgentList;
                    dgvData = dgvDataMonthlyAgentList;

                }
                else if (tabNumber == 8)
                {
                    dtiFromMonth = dtCRFromMonth;
                    dtiToMonth = dtCRToMonth;
                    txtSalesCode = txtCRSaleCode;
                    dgvData = dgvCRList;
                    cbbField = cbCRField;
                }
                else if (tabNumber == 9)
                {
                    txtFileName = txtAttendanceFileName;
                    txtSalesCode = txtAttendanceSaleCode;
                    dtiFromMonth = dtAttendanceFromMonth;
                    dtiToMonth = dtAttendanceToMonth;
                    cbViewAll = cbAttendanceViewAll;
                    cbbField = cbAttendanceField;
                    dgvFile = dgvAttendanceFile;
                    dgvData = dgvAttendanceData;
                }
                dtiFromMonth.Value = DateTime.Now;
                dtiToMonth.Value = DateTime.Now;
                cbViewAll.Enabled = false;
            }
            catch (Exception ex) { throw; }
        }

        /// Load list of Batch File to DataGridView by fileType
        /// <param name="fileType">Use variable Constant.TYPE_[abcd]for this parameter</param>
        private DataTable LoadFileList(string fileType)
        {
            BatchFileSaleInfoUploadBLL batchFileSaleInfoUploadBll = new BatchFileSaleInfoUploadBLL();
            DataTable dtFileList = new DataTable();

            dtFileList.Columns.Add("Batch File");
            dtFileList.Columns.Add("File Name");
            dtFileList.Columns.Add("Created Date");

            foreach (DataRow row in batchFileSaleInfoUploadBll.GetBatchFileByType(fileType).Rows)
            {
                string[] rowData = new string[] { row["BatchFileSaleInfoID"].ToString(), row["FileName"].ToString(), row["CreatedDate"].ToString() };
                dtFileList.Rows.Add(rowData);
            }

            dgvFile.DataSource = null;
            dgvFile.Columns.Clear();
            dgvFile.DataSource = dtFileList;
            dgvFile.AutoResizeColumns();

            dgvFile.Sort(this.dgvFile.Columns["Created Date"], ListSortDirection.Descending);
            if (dgvFile.Rows.Count > 0)
                dgvFile.Rows[0].Selected = true;

            return dtFileList;
        }

        //Get SaleCode - PersonalCode Mapping 
        private void SaleCodeMapping()
        {
            try
            {
                if (Mapping_SaleCode_PersonalCode == null)
                    Mapping_SaleCode_PersonalCode = new Dictionary<string, string>();
                else
                    Mapping_SaleCode_PersonalCode.Clear();

                if (Mapping_SaleCode_ProfileID == null)
                    Mapping_SaleCode_ProfileID = new Dictionary<string, string>();
                else
                    Mapping_SaleCode_ProfileID.Clear();

                DataTable dt = saleInfoBll.GetSaleCodeMapping();
                foreach (DataRow row in dt.Rows)
                {
                    Mapping_SaleCode_PersonalCode.Add(row["SaleCode"].ToString(), row["PersonalCode"].ToString());
                    Mapping_SaleCode_ProfileID.Add(row["SaleCode"].ToString(), row["ProfileID"].ToString());
                }
            }
            catch (Exception ex) { throw ex; }
        }

        //Get Sale data to Datatable
        private void LoadSaleData()
        {
            try
            {
                DataTable saleData = new DataTable();
                if (dgvFile.Rows.Count > 0)
                {
                    DataGridViewRow row = dgvFile.SelectedRows[0];
                    string batchFileId = row.Cells[0].Value.ToString();

                    if (tabNumber == 0)
                    {
                        SalePIPBLL salePIPBll = new SalePIPBLL();
                        saleData = salePIPBll.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    else if (tabNumber == 1)
                    {
                        SaleSQMBLL saleSQMBll = new SaleSQMBLL();
                        saleData = saleSQMBll.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    else if (tabNumber == 2)
                    {
                        SaleDISBLL saleDISBll = new SaleDISBLL();
                        saleData = saleDISBll.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    else if (tabNumber == 3)
                    {
                        SalePaymentBLL salePaymentBll = new SalePaymentBLL();
                        saleData = salePaymentBll.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    else if (tabNumber == 5)
                    {
                        TreeNode selectedNode = tvAgent.SelectedNode;
                        string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                        if (selectedNodeText.Equals("Cards Activation Rate"))
                        {
                            AgentCardsActivationRateBLL agentCardsActivationRateBLL = new AgentCardsActivationRateBLL();
                            saleData = agentCardsActivationRateBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Cards"))
                        {
                            AgentCardsBLL agentCardsBll = new AgentCardsBLL();
                            saleData = agentCardsBll.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Personal Loan"))
                        {
                            AgentPersonalLoanBLL agentPersonalLoanBll = new AgentPersonalLoanBLL();
                            saleData = agentPersonalLoanBll.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("LOC"))
                        {
                            AgentLOCBLL agentLOCSuppCardBLL = new AgentLOCBLL();
                            saleData = agentLOCSuppCardBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Supp Card"))
                        {
                            AgentSuppCardBLL agentSuppCardBLL = new AgentSuppCardBLL();
                            saleData = agentSuppCardBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Field Sales"))
                        {
                            AgentFieldSalesBLL agentFieldSalesBLL = new AgentFieldSalesBLL();
                            saleData = agentFieldSalesBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                    }
                    else if (tabNumber == 6)
                    {
                        TreeNode selectedNode = tvTeamLd.SelectedNode;
                        string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                        if (selectedNodeText.Equals("Cards"))
                        {
                            TeamLeaderCardsBLL teamLeaderCardsBLL = new TeamLeaderCardsBLL();
                            saleData = teamLeaderCardsBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Field Sales"))
                        {
                            TeamLeaderFieldSalesBLL teamLeaderFieldSalesBLL = new TeamLeaderFieldSalesBLL();
                            saleData = teamLeaderFieldSalesBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("PL"))
                        {
                            TeamLeaderPLBLL teamLeaderPLBLL = new TeamLeaderPLBLL();
                            saleData = teamLeaderPLBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                        else if (selectedNodeText.Equals("Portfolio"))
                        {
                            TeamLeaderPortfolioBLL teamLeaderPortfolioBLL = new TeamLeaderPortfolioBLL();
                            saleData = teamLeaderPortfolioBLL.GetByBatchFileSaleInfoID(batchFileId);
                        }
                    }
                    else if (tabNumber == 7)
                    {
                        MonthlyAgentListBLL monthlyAgentListBLL = new MonthlyAgentListBLL();
                        saleData = monthlyAgentListBLL.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    else if (tabNumber == 9)
                    {
                        SaleAttendanceBLL saleAttendanceBLL = new SaleAttendanceBLL();
                        saleData = saleAttendanceBLL.GetByBatchFileSaleInfoID(batchFileId);
                    }
                    BuildGridViewSaleData(saleData);
                }
            }
            catch (Exception e) { MessageBox.Show(e.StackTrace); }
        }

        //Load GridView Sale via DataTable
        private void BuildGridViewSaleData(DataTable tbData)
        {
            DataTable dt = new DataTable();
            if (tabNumber == 0)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Profile ID");
                //dt.Columns.Add("Personal Code");
                dt.Columns.Add("Role");
                dt.Columns.Add("Employee Full Name");
                dt.Columns.Add("Level");
                dt.Columns.Add("PIP Target");
                dt.Columns.Add("PIP M1 Result");
                dt.Columns.Add("PIP M2 Result");
                dt.Columns.Add("Current Month PIP Status");
                dt.Columns.Add("Batch File Sale Info ID");

                foreach (DataRow row in tbData.Rows)
                {
                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["SaleCode"].ToString(), row["ProfileID"].ToString()//, row["PersonalCode"].ToString()
                        , row["Role"].ToString(), row["EmployeeFullName"].ToString(), row["Level"].ToString(),
                        row["PIPTarget"].ToString(), row["PIPM1Result"].ToString(), row["PIPM2Result"].ToString(), row["CurrentMonthPIPStatus"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            else if (tabNumber == 1)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Sale Name");
                dt.Columns.Add("Profile ID"); //23Jan2015
                dt.Columns.Add("Channel");
                dt.Columns.Add("Role");
                dt.Columns.Add("Grand Total Risk Point");
                dt.Columns.Add("Incentive ClawBack");
                dt.Columns.Add("Compliance Gate Keeper");
                dt.Columns.Add("Disciplinary Implications");
                dt.Columns.Add("Batch File Sale Info ID");

                foreach (DataRow row in tbData.Rows)
                {
                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["SaleCode"].ToString(), row["SaleName"].ToString()
                        , row["ProfileID"].ToString() //23Jan2015
                        , row["Channel"].ToString(), row["Role"].ToString(), row["GrandTotalRiskPoint"].ToString(), 
                        row["IncentiveClawBack"].ToString(), row["ComplianceGateKeeper"].ToString(), row["DisciplinaryImplications"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            else if (tabNumber == 2)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Sale Name");
                dt.Columns.Add("Profile ID"); //23Jan2015
                dt.Columns.Add("Disciplinary Action");
                dt.Columns.Add("Mistakes");
                dt.Columns.Add("Reporting Date");
                dt.Columns.Add("Action Required Date");
                dt.Columns.Add("Completion Status");
                dt.Columns.Add("Batch File Sale Info ID");

                foreach (DataRow row in tbData.Rows)
                {
                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["SaleCode"].ToString(), row["SaleName"].ToString()
                        , row["ProfileID"].ToString()
                        , row["DisciplinaryAction"].ToString(), row["Mistakes"].ToString(),
                        Convert.ToDateTime(row["ReportingDate"]).ToShortDateString(), Convert.ToDateTime(row["ActionRequiredDate"]).ToShortDateString(), row["CompletionStatus"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            else if (tabNumber == 3)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Inc Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Profile ID");
                dt.Columns.Add("Employee FullName");
                dt.Columns.Add("Contract Salary");
                dt.Columns.Add("BUIP NetScore");
                dt.Columns.Add("Performance Grade");
                dt.Columns.Add("Perf Incentive");
                dt.Columns.Add("Deferral Pay");
                dt.Columns.Add("SQM Deduction");
                dt.Columns.Add("SQM Bonus");
                dt.Columns.Add("Inactive Card Clawback");
                dt.Columns.Add("Adjustment");
                dt.Columns.Add("Incentive Act");
                dt.Columns.Add("Incentive Paid");
                dt.Columns.Add("Actual WDs");
                dt.Columns.Add("Paid WDs");
                dt.Columns.Add("Base");
                dt.Columns.Add("Allowances");
                dt.Columns.Add("_13thSalary Quit Settlement");
                dt.Columns.Add("NonIncentives");
                dt.Columns.Add("SHUI Union Fee");
                dt.Columns.Add("Insurance");
                dt.Columns.Add("Total Insurances");
                dt.Columns.Add("HR Service Fee");
                dt.Columns.Add("Gross Employee Income");
                dt.Columns.Add("Payroll Cost");
                dt.Columns.Add("Remarks");
                dt.Columns.Add("Batch File ID");

                double buipNetScore;
                foreach (DataRow row in tbData.Rows)
                {
                    //Process % Rate
                    double.TryParse(row["BUIPNetScore"].ToString(), out buipNetScore);
                    buipNetScore = Math.Round(buipNetScore * 100, 0);

                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["IncMonth"].ToString(), row["SaleCode"].ToString()
                        , row["ProfileID"].ToString()
                        , row["EmployeeFullNameE"].ToString(), row["ContractSalary"].ToString(), buipNetScore.ToString() + "%",
                        row["PerformanceGrade"].ToString(), row["PerfIncentive"].ToString(), row["DeferralPay"].ToString(), row["SQMDeduction"].ToString(), row["SQMBonus"].ToString(),
                        row["InactiveCardClawback"].ToString(), row["Adjustment"].ToString(), row["IncentiveAct"].ToString(), row["IncentivePaid"].ToString(), row["ActualWDs"].ToString(),
                        row["PaidWDs"].ToString(), row["Base"].ToString(), row["Allowances"].ToString(), row["_13thSalaryQuitSettlement"].ToString(), row["NonIncentives"].ToString(),
                        row["SHUIUnionFee"].ToString(), row["Insurance"].ToString(), row["TotalInsurances"].ToString(), row["HRServiceFee"].ToString(), row["GrossEmployeeIncome"].ToString(),
                        row["PayrollCost"].ToString(), row["Remarks"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            else if (tabNumber == 5)
            {
                TreeNode selectedNode = tvAgent.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                if (string.IsNullOrEmpty(selectedNodeText) || selectedNodeText.Equals("Cards Activation Rate"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Inactive Cards");
                    dt.Columns.Add("MOB");
                    dt.Columns.Add("Batch File ID");

                    foreach (DataRow row in tbData.Rows)
                    {
                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString()
                            , row["ProfileID"].ToString()
                            , row["InactiveCards"].ToString(), row["MOB"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Cards"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Employee Name");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("Classic");
                    dt.Columns.Add("Gold");
                    dt.Columns.Add("Platinum");
                    dt.Columns.Add("Travel Platinum");
                    dt.Columns.Add("Supplimentary");
                    dt.Columns.Add("Card Approval Rate");
                    dt.Columns.Add("NTB Cards");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore, cardApprovalrate;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["Overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);
                        double.TryParse(row["CardApprovalrate"].ToString(), out cardApprovalrate);
                        cardApprovalrate = Math.Round(cardApprovalrate * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString()
                            , row["ProfileID"].ToString()
                            , row["EmployeeName"].ToString(), overallNetScore.ToString() + "%", row["Classic"].ToString(), row["Gold"].ToString(),
                            row["Platinum"].ToString(), row["TravelPlatinum"].ToString(), row["Supplimentary"].ToString(), cardApprovalrate.ToString() + "%", row["NTBCards"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Personal Loan"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("NAME");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("PL Approval Rate");
                    dt.Columns.Add("Pl Amount");
                    dt.Columns.Add("Pl Count");
                    dt.Columns.Add("Xsell");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore, plApprovalrate;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["Overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);
                        double.TryParse(row["PLApprovalrate"].ToString(), out plApprovalrate);
                        plApprovalrate = Math.Round(plApprovalrate * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString(), row["NAME"].ToString()
                            , row["ProfileID"].ToString()
                            , overallNetScore.ToString() + "%", plApprovalrate.ToString() + "%", row["Plamount"].ToString(),
                            row["Plcount"].ToString(), row["Xsell"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("LOC"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Name");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("LOC Amount");
                    dt.Columns.Add("Supplimentary");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["Overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString(), row["NAME"].ToString()
                            , row["ProfileID"].ToString()
                            , overallNetScore.ToString() + "%", row["LOCamount"].ToString(),
                            row["Supplimentary"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Supp Card"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("NAME");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("Supplimentary");
                    dt.Columns.Add("Supp Approval");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["Overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString(), row["NAME"].ToString()
                            , row["ProfileID"].ToString()
                            , overallNetScore.ToString() + "%", row["Supplimentary"].ToString(),
                            row["Supp_approval"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Field Sales"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Name");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("NTB");
                    dt.Columns.Add("Pl Count");
                    dt.Columns.Add("LOC Supp");
                    dt.Columns.Add("Total Deals");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["Overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString(), row["NAME"].ToString()
                            , row["ProfileID"].ToString()
                            , overallNetScore.ToString() + "%", row["NTB"].ToString(), row["Plcount"].ToString(),
                            row["LOCSupp"].ToString(), row["TotalDeals"].ToString(), row["BatchFileSaleInfoID"].ToString() 
                        };
                        dt.Rows.Add(rowData);
                    }
                }
            }
            else if (tabNumber == 6)
            {
                TreeNode selectedNode = tvTeamLd.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                if (string.IsNullOrEmpty(selectedNodeText) || selectedNodeText.Equals("Cards"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Name");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Level");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("Agenton Target");
                    dt.Columns.Add("NTB");
                    dt.Columns.Add("Team Size");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString(), row["Name"].ToString()
                            , row["ProfileID"].ToString()
                            , row["Level"].ToString(), overallNetScore.ToString() + "%", row["AgentonTarget"].ToString(),
                            row["NTB"].ToString(), row["Teamsize"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Field Sales"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Employee Name");
                    dt.Columns.Add("Level");
                    dt.Columns.Add("Category");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("Agenton Target");
                    dt.Columns.Add("Team Approval");
                    dt.Columns.Add("Team Size");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString()
                            , row["ProfileID"].ToString()
                            , row["EmployeeName"].ToString(), row["Level"].ToString(), row["Category"].ToString(), overallNetScore.ToString() + "%",
                            row["AgentonTarget"].ToString(), row["Teamapproval"].ToString(), row["Teamsize"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("PL"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Employee Name");
                    dt.Columns.Add("Level");
                    dt.Columns.Add("Category");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("PL Amount");
                    dt.Columns.Add("PL Count");
                    dt.Columns.Add("Agenton Target");
                    dt.Columns.Add("Team Size");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString()
                            , row["ProfileID"].ToString()
                            , row["EmployeeName"].ToString(), row["Level"].ToString(), row["Category"].ToString(), overallNetScore.ToString() + "%",
                            row["Plamount"].ToString(), row["Plcount"].ToString(), row["AgentonTarget"].ToString(), row["Teamsize"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
                else if (selectedNodeText.Equals("Portfolio"))
                {
                    dt.Columns.Add("Month");
                    dt.Columns.Add("Sale Code");
                    dt.Columns.Add("Profile ID");
                    dt.Columns.Add("Employee Name");
                    dt.Columns.Add("Level");
                    dt.Columns.Add("Overall Net Score");
                    dt.Columns.Add("LO CAmount");
                    dt.Columns.Add("NTB");
                    dt.Columns.Add("Supplimentary");
                    dt.Columns.Add("Pl Amount");
                    dt.Columns.Add("Agenton Target");
                    dt.Columns.Add("Team Size");
                    dt.Columns.Add("Batch File ID");

                    double overallNetScore;
                    foreach (DataRow row in tbData.Rows)
                    {
                        //Process % Rate
                        double.TryParse(row["overallnetscore"].ToString(), out overallNetScore);
                        overallNetScore = Math.Round(overallNetScore * 100, 0);

                        string[] rowData = new string[] {
                            row["Month"].ToString(), row["SaleCode"].ToString()
                            , row["ProfileID"].ToString()
                            , row["EmployeeName"].ToString(), row["Level"].ToString(), overallNetScore.ToString() + "%", row["LOCamount"].ToString(),
                            row["NTB"].ToString(), row["Supplimentary"].ToString(), row["Plamount"].ToString(), row["AgentonTarget"].ToString(), row["Teamsize"].ToString(), row["BatchFileSaleInfoID"].ToString()
                        };
                        dt.Rows.Add(rowData);
                    }
                }
            }
            else if (tabNumber == 7)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Profile ID");
                dt.Columns.Add("Employee FullName");
                dt.Columns.Add("Role");
                dt.Columns.Add("Level");
                dt.Columns.Add("Category");
                dt.Columns.Add("Product Category");
                dt.Columns.Add("Product");
                dt.Columns.Add("TeamName");
                dt.Columns.Add("TeamLeader Code");
                dt.Columns.Add("Channel");
                dt.Columns.Add("Joining Date");
                dt.Columns.Add("Promotion Role Transfer Date");
                dt.Columns.Add("ID Number");
                dt.Columns.Add("Status");
                dt.Columns.Add("Actual Lastworking Date");
                dt.Columns.Add("Contract Termination Date");
                dt.Columns.Add("PaidWDs");
                dt.Columns.Add("ActualWDs");
                dt.Columns.Add("Progressive Target");
                dt.Columns.Add("PIP");
                dt.Columns.Add("Batch File Sale Info ID");

                foreach (DataRow row in tbData.Rows)
                {
                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["SaleCode"].ToString()
                        , row["ProfileID"].ToString()
                        , row["EmployeeFullName"].ToString(), row["Role"].ToString(), row["Level"].ToString(), row["Category"].ToString(), row["ProductCategory"].ToString(),
                        row["Product"].ToString(), row["TeamName"].ToString(), row["TeamLeaderCode"].ToString(), row["Channel"].ToString(), row["JoiningDate"].ToString(), row["PromotionRoleTransferDate"].ToString(),
                        row["IDNumber"].ToString(), row["Status"].ToString(), row["ActualLastworkingDate"].ToString(), row["ContractTerminationDate"].ToString(), row["PaidWDs"].ToString(),
                        row["ActualWDs"].ToString(), row["ProgressiveTarget"].ToString(), row["PIP"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            else if (tabNumber == 9)
            {
                dt.Columns.Add("Month");
                dt.Columns.Add("Sale Code");
                dt.Columns.Add("Profile ID");
                dt.Columns.Add("Actual Working Day");
                dt.Columns.Add("Paid WDs");
                dt.Columns.Add("Batch File ID");

                foreach (DataRow row in tbData.Rows)
                {
                    string[] rowData = new string[] {
                        row["Month"].ToString(), row["SaleCode"].ToString()
                        , row["ProfileID"].ToString()
                        , row["ActualWorkingDay"].ToString(), row["PaidWDs"].ToString(), row["BatchFileSaleInfoID"].ToString()
                    };
                    dt.Rows.Add(rowData);
                }
            }
            dgvData.DataSource = null;
            dgvData.Columns.Clear();
            dgvData.DataSource = dt;
            dgvData.AutoResizeColumns();

            //Resize or set alignment of columm in tab Agent and TeamLeader 
            InitSaleProductivityColumn();

            //Fullfill datagridView if width of all columns is less than datagridview width
            int headerWidth = 0;
            dgvData.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => headerWidth += col.Width);
            DataGridViewColumn lastCol = dgvData.Columns.GetLastColumn(DataGridViewElementStates.Visible, DataGridViewElementStates.None);
            lastCol.Width = (dgvData.Width - headerWidth) > 0 ? lastCol.Width + (dgvData.Width - headerWidth) : lastCol.Width;
            LoadComboBoxField();
        }

        //Resize or set alignment of columm in tab Agent and TeamLeader 
        private void InitSaleProductivityColumn()
        {
            if (tabNumber == 0)
            {
                dgvData.Columns["PIP Target"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["PIP M1 Result"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["PIP M2 Result"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
            else if (tabNumber == 1)
            {
                dgvData.Columns["Grand Total Risk Point"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
            else if (tabNumber == 3)
            {
                dgvData.Columns["Contract Salary"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["BUIP NetScore"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Perf Incentive"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Deferral Pay"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["SQM Deduction"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["SQM Bonus"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Inactive Card Clawback"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Adjustment"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Incentive Act"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Incentive Paid"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Actual WDs"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Paid WDs"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Base"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Allowances"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["_13thSalary Quit Settlement"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["NonIncentives"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["SHUI Union Fee"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Insurance"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Total Insurances"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["HR Service Fee"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Gross Employee Income"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Payroll Cost"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
            else if (tabNumber == 5)
            {
                TreeNode selectedNode = tvAgent.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                if (selectedNodeText.Equals("Cards Activation Rate"))
                {
                    dgvData.Columns["Inactive Cards"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Cards"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Classic"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Gold"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Platinum"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Travel Platinum"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Supplimentary"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Card Approval Rate"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["NTB Cards"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Personal Loan"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["PL Approval Rate"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Pl Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Pl Count"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Xsell"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("LOC"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["LOC Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Supplimentary"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Supp Card"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Supplimentary"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Supp Approval"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Field Sales"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["NTB"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Pl Count"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["LOC Supp"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Total Deals"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
            }
            else if (tabNumber == 6)
            {
                TreeNode selectedNode = tvTeamLd.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                if (selectedNodeText.Equals("Cards"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Agenton Target"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["NTB"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Team Size"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Field Sales"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Agenton Target"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Team Approval"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Team Size"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("PL"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["PL Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["PL Count"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Agenton Target"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Team Size"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
                else if (selectedNodeText.Equals("Portfolio"))
                {
                    dgvData.Columns["Overall Net Score"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["LO CAmount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["NTB"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Supplimentary"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Pl Amount"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Agenton Target"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                    dgvData.Columns["Team Size"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                }
            }
            else if (tabNumber == 7)
            {
                dgvData.Columns["ActualWDs"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["PaidWDs"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
            else if (tabNumber == 9)
            {
                dgvData.Columns["Actual Working Day"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dgvData.Columns["Paid WDs"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
        }

        private void BrowseFile()
        {
            try
            {
                OpenFileDialog ExcelDialog = new OpenFileDialog();
                ExcelDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
                ExcelDialog.InitialDirectory = appParam.PathFile;
                ExcelDialog.Title = "Select your file excel";
                if (ExcelDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    txtFileName.Text = ExcelDialog.FileName;
                    txtFileName.ReadOnly = true;

                    var lastPath = txtFileName.Text;
                    lastPath = lastPath.Substring(0, lastPath.LastIndexOf("\\"));
                    if (appParam.PathFile != lastPath)
                        appParam.PathFile = lastPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        // Add or remove a column in DataGridView sale data
        // <param name="op">1 is Add a column. 2 is remove a column</param>
        private void ModifyGridViewColumns(int op)
        {
            try
            {
                //Add or remove columns were chosen to/from List
                if (cbbField.SelectedIndex >= 0)
                {
                    cbViewAll.Enabled = true;
                    string selectedItem = cbbField.SelectedItem.ToString();
                    foreach (DataGridViewColumn col in dgvData.Columns)
                    {
                        if (col.HeaderText.Equals(selectedItem))
                        {
                            if (op == 1)
                            {
                                if (!colIndex.Contains(col.Index))
                                {
                                    colIndex.Add(col.Index);
                                }
                            }
                            else if (op == 2)
                            {
                                colIndex.Remove(col.Index);
                            }
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Please choose a column name", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                //Set status of CheckBox ViewAll
                dgvData.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => col.Visible = false);
                for (int i = 0; i < colIndex.Count; i++)
                {
                    dgvData.Columns[colIndex[i]].Visible = true;
                }

                int count = 0;
                foreach (DataGridViewColumn col in dgvData.Columns)
                {
                    if (col.Visible)
                    {
                        count++;
                    }
                }
                cbViewAll.Checked = count == dgvData.Columns.Count ? true : false;
            }
            catch (Exception ex) { throw; }
        }

        private void LoadComboBoxField()
        {
            try
            {
                cbbField.Items.Clear();
                foreach (DataGridViewColumn col in dgvData.Columns)
                    cbbField.Items.Add(col.HeaderText);
                if (cbbField.Items.Count > 0)
                    cbbField.SelectedIndex = 0;
            }
            catch (Exception ex) { throw ex; }
        }

        private void LoadCustomReportCmbField(int typeReport)
        {
            cbbField.DataSource = null;
            cbbField.Items.Clear();

            try
            {
                if (typeReport == 0)
                {

                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("Role", "Role"));
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeFullName", "Employee Full Name"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("PIPTarget", "PIP Target"));
                    dataSource.Add(new KeyValuePair<string, string>("PIPM1Result", "PIP M1 Resulte"));
                    dataSource.Add(new KeyValuePair<string, string>("PIPM2Result", "PIP M2 Result"));
                    dataSource.Add(new KeyValuePair<string, string>("CurrentMonthPIPStatus", "Current Month PIP Status"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 1)
                {

                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("SaleName", "Sale Name"));
                    dataSource.Add(new KeyValuePair<string, string>("Channel", "Channel"));
                    dataSource.Add(new KeyValuePair<string, string>("Role", "Role"));
                    dataSource.Add(new KeyValuePair<string, string>("GrandTotalRiskPoint", "Grand Total Risk Point"));
                    dataSource.Add(new KeyValuePair<string, string>("IncentiveClawBack", "Incentive ClawBack"));

                    dataSource.Add(new KeyValuePair<string, string>("ComplianceGateKeeper", "Compliance Gate Keeper"));
                    dataSource.Add(new KeyValuePair<string, string>("DisciplinaryImplications", "Disciplinary Implications"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 2)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("SaleName", "Sale Name"));
                    dataSource.Add(new KeyValuePair<string, string>("DisciplinaryAction", "Disciplinary Action"));
                    dataSource.Add(new KeyValuePair<string, string>("Mistakes", "Mistakes"));
                    dataSource.Add(new KeyValuePair<string, string>("ReportingDate", "Reporting Date"));
                    dataSource.Add(new KeyValuePair<string, string>("ActionRequiredDate", "Action Required Date"));

                    dataSource.Add(new KeyValuePair<string, string>("CompletionStatus", "Completion Status"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 3)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("PayMonth", "PayMonth"));
                    dataSource.Add(new KeyValuePair<string, string>("IncMonth", "IncMonth"));

                    dataSource.Add(new KeyValuePair<string, string>("EmployeeFullNameE", "Employee FullName E"));
                    dataSource.Add(new KeyValuePair<string, string>("ContractSalary", "Contract Salary"));

                    dataSource.Add(new KeyValuePair<string, string>("BUIPNetScore", "BUIP NetScore"));
                    dataSource.Add(new KeyValuePair<string, string>("PerformanceGrade", "Performance Grade"));
                    dataSource.Add(new KeyValuePair<string, string>("PerfIncentive", "Perf Incentive"));
                    dataSource.Add(new KeyValuePair<string, string>("DeferralPay", "Deferral Pay"));
                    dataSource.Add(new KeyValuePair<string, string>("SQMDeduction", "SQM Deduction"));
                    dataSource.Add(new KeyValuePair<string, string>("InactiveCardClawback", "Inactive Card Clawback"));
                    dataSource.Add(new KeyValuePair<string, string>("Adjustment", "Adjustment"));
                    dataSource.Add(new KeyValuePair<string, string>("IncentiveAct", "Incentive Act"));
                    dataSource.Add(new KeyValuePair<string, string>("IncentivePaid", "Incentive Paid"));
                    dataSource.Add(new KeyValuePair<string, string>("ActualWDs", "Actual WDs"));
                    dataSource.Add(new KeyValuePair<string, string>("PaidWDs", "Paid WDs"));
                    dataSource.Add(new KeyValuePair<string, string>("Base", "Base"));
                    dataSource.Add(new KeyValuePair<string, string>("Allowances", "Allowances"));
                    dataSource.Add(new KeyValuePair<string, string>("_13thSalaryQuitSettlement", "_13thSalary Quit Settlement"));
                    dataSource.Add(new KeyValuePair<string, string>("NonIncentives", "NonIncentives"));
                    dataSource.Add(new KeyValuePair<string, string>("SHUIUnionFee", "SHUI Union Fee"));
                    dataSource.Add(new KeyValuePair<string, string>("Insurance", "Insurance"));
                    dataSource.Add(new KeyValuePair<string, string>("TotalInsurances", "Total Insurances"));
                    dataSource.Add(new KeyValuePair<string, string>("HRServiceFee", "HR Service Fee"));
                    dataSource.Add(new KeyValuePair<string, string>("GrossEmployeeIncome", "Gross Employee Income"));
                    dataSource.Add(new KeyValuePair<string, string>("PayrollCost", "qqPayroll Cost"));
                    dataSource.Add(new KeyValuePair<string, string>("Remarks", "Remarks"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 4)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    //dataSource.Add(new KeyValuePair<string, string>("CurrentSalesCode", "Current Sales Code"));
                    dataSource.Add(new KeyValuePair<string, string>("InactiveCards", "Inactive Cards"));
                    dataSource.Add(new KeyValuePair<string, string>("MOB", "MOB"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 5)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    //dataSource.Add(new KeyValuePair<string, string>("CurrentSalesCode", "Current Sales Code"));
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeName", "EmployeeName"));
                    dataSource.Add(new KeyValuePair<string, string>("Overallnetscore", "Overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("Classic", "Classic"));
                    dataSource.Add(new KeyValuePair<string, string>("Gold", "Gold"));
                    dataSource.Add(new KeyValuePair<string, string>("Platinum", "Platinum"));
                    dataSource.Add(new KeyValuePair<string, string>("TravelPlatinum", "TravelPlatinum"));
                    dataSource.Add(new KeyValuePair<string, string>("Supplimentary", "Supplimentary"));
                    dataSource.Add(new KeyValuePair<string, string>("CardApprovalrate", "CardApprovalrate"));
                    dataSource.Add(new KeyValuePair<string, string>("NTBCards", "NTBCards"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));

                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 6)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    //dataSource.Add(new KeyValuePair<string, string>("code", "code"));
                    dataSource.Add(new KeyValuePair<string, string>("NAME", "NAME"));
                    dataSource.Add(new KeyValuePair<string, string>("Overallnetscore", "Overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("PLApprovalrate", "PLApprovalrate"));
                    dataSource.Add(new KeyValuePair<string, string>("Plamount", "Plamount"));
                    dataSource.Add(new KeyValuePair<string, string>("Plcount", "Plcount"));
                    dataSource.Add(new KeyValuePair<string, string>("Xsell", "Xsell"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));


                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 7)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();

                    dataSource.Add(new KeyValuePair<string, string>("NAME", "NAME"));
                    dataSource.Add(new KeyValuePair<string, string>("Overallnetscore", "Overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("LOCamount", "LOCamount"));
                    dataSource.Add(new KeyValuePair<string, string>("Supplimentary", "Supplimentary"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 8)
                {

                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("NAME", "NAME"));
                    dataSource.Add(new KeyValuePair<string, string>("Overallnetscore", "Overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("Supplimentary", "Supplimentary"));
                    dataSource.Add(new KeyValuePair<string, string>("Supp_approval", "Supp_approval"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 9)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    //dataSource.Add(new KeyValuePair<string, string>("code", "code"));
                    dataSource.Add(new KeyValuePair<string, string>("NAME", "NAME"));
                    dataSource.Add(new KeyValuePair<string, string>("Overallnetscore", "Overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("NTB", "NTB"));
                    dataSource.Add(new KeyValuePair<string, string>("Plcount", "Plcount"));
                    dataSource.Add(new KeyValuePair<string, string>("LOCSupp", "LOCSupp"));
                    dataSource.Add(new KeyValuePair<string, string>("TotalDeals", "TotalDeals"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));

                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 10)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("NAME", "NAME"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("overallnetscore", "overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("AgentonTarget", "AgentonTarget"));
                    dataSource.Add(new KeyValuePair<string, string>("NTB", "NTB"));
                    dataSource.Add(new KeyValuePair<string, string>("Teamsize", "Teamsize"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 11)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeName", "Employee Name"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("Category", "Category"));
                    dataSource.Add(new KeyValuePair<string, string>("overallnetscore", "overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("AgentonTarget", "AgentonTarget"));
                    dataSource.Add(new KeyValuePair<string, string>("Teamapproval", "Teamapproval"));
                    dataSource.Add(new KeyValuePair<string, string>("Teamsize", "Teamsize"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 12)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeName", "Employee Name"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("Category", "Category"));
                    dataSource.Add(new KeyValuePair<string, string>("overallnetscore", "overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("Plamount", "Plamount"));
                    dataSource.Add(new KeyValuePair<string, string>("Plcount", "Plcount"));
                    dataSource.Add(new KeyValuePair<string, string>("AgentonTarget", "AgentonTarget"));
                    dataSource.Add(new KeyValuePair<string, string>("Teamsize", "Teamsize"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";

                }
                else if (typeReport == 13)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeName", "Employee Name"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("overallnetscore", "overallnetscore"));
                    dataSource.Add(new KeyValuePair<string, string>("LOCamount", "LOCamount"));
                    dataSource.Add(new KeyValuePair<string, string>("NTB", "NTB"));
                    dataSource.Add(new KeyValuePair<string, string>("Supplimentary", "Supplimentary"));
                    dataSource.Add(new KeyValuePair<string, string>("Plamount", "Plamount"));
                    dataSource.Add(new KeyValuePair<string, string>("AgentonTarget", "AgentonTarget"));
                    dataSource.Add(new KeyValuePair<string, string>("Teamsize", "Teamsize"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "BatchFileSaleInfoID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";


                }
                else if (typeReport == 14)
                {
                    var dataSource = new BindingList<KeyValuePair<string, string>>();
                    dataSource.Add(new KeyValuePair<string, string>("EmployeeFullName", "Employee FullName"));
                    dataSource.Add(new KeyValuePair<string, string>("Role", "MAL Role"));
                    dataSource.Add(new KeyValuePair<string, string>("Level", "Level"));
                    dataSource.Add(new KeyValuePair<string, string>("Category", "Category"));
                    dataSource.Add(new KeyValuePair<string, string>("ProductCategory", "Product Category"));
                    dataSource.Add(new KeyValuePair<string, string>("Product", "Product"));
                    dataSource.Add(new KeyValuePair<string, string>("TeamName", "TeamName"));
                    dataSource.Add(new KeyValuePair<string, string>("TeamLeaderCode", "TeamLeader Code"));
                    dataSource.Add(new KeyValuePair<string, string>("Channel", "Channel"));
                    dataSource.Add(new KeyValuePair<string, string>("JoiningDate", "Joining Date"));
                    dataSource.Add(new KeyValuePair<string, string>("PromotionRoleTransferDate", "Promotion Role Transfer Date"));
                    dataSource.Add(new KeyValuePair<string, string>("IDNumber", "ID Number"));
                    dataSource.Add(new KeyValuePair<string, string>("Status", "Status"));
                    dataSource.Add(new KeyValuePair<string, string>("ActualLastworkingDate", "Actual Lastworking Date"));
                    dataSource.Add(new KeyValuePair<string, string>("ContractTerminationDate", "Contract Termination Date"));
                    dataSource.Add(new KeyValuePair<string, string>("PaidWDs", "PaidWDs"));
                    dataSource.Add(new KeyValuePair<string, string>("ActualWDs", "ActualWDs"));
                    dataSource.Add(new KeyValuePair<string, string>("ProgressiveTarget", "Progressive Target"));
                    dataSource.Add(new KeyValuePair<string, string>("PIP", "PIP"));
                    dataSource.Add(new KeyValuePair<string, string>("BatchFileSaleInfoID", "Batch File Sale Info ID"));
                    cbbField.DataSource = dataSource;
                    cbbField.ValueMember = "Key";
                    cbbField.DisplayMember = "Value";
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void CheckBoxCheckedChange()
        {
            try
            {
                if (cbViewAll.Checked)
                {
                    dgvData.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => col.Visible = true);
                }
                else
                {
                    dgvData.Columns.OfType<DataGridViewColumn>().ToList().ForEach(col => col.Visible = false);
                    colIndex.ToList().ForEach(col => dgvData.Columns[col].Visible = true);
                    if (colIndex.Count == dgvData.Columns.Count)
                    {
                        MessageBox.Show("All columns was added to view");
                        cbViewAll.Checked = true;
                    }
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private string formatDateToReportDateString(DateTime dDate)
        {
            if (dDate == null)
                return "";
            return dDate.Year.ToString() + " M" + dDate.Month.ToString();
        }

        //Get Month of file when Upload
        private bool GetFileMonth(string fileName)
        {
            try
            {
                currentDateOfFile = DateTime.Now.Year.ToString() + " M" + DateTime.Now.Month.ToString();
                string strMonth = fileName.Substring(fileName.Length - 10, 6);
                int numConvert;
                if (int.TryParse(strMonth, out numConvert))
                {
                    currentDateOfFile = strMonth.Substring(0, 4) + " M" + strMonth.Substring(4, 2);
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex) { throw ex; }
        }

        //Check max file size when upload
        private bool checkFileSizeUpload(string filePath)
        {
            try
            {
                ConfigurationBLL configurationBLL = new ConfigurationBLL();
                Configuration configuration = configurationBLL.GetByKey(Configuration.MAX_FILE_SIZE_UPLOAD);
                string maxFileSize = configuration.ConfigValue;
                FileInfo fileInfo = new FileInfo(filePath);
                if (fileInfo.Length / 1024 > Convert.ToDouble(maxFileSize))
                {
                    MessageBox.Show("The file size is greater than Maximum, please select another file.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
                return true;
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region PIPREPORT

        private void tabPIP_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(0);
                LoadFileList(Constant.TYPE_PIP);
                LoadSaleData();
                LoadComboBoxField();
                LoadPIPStatus();
            }
            catch (Exception ex) { throw ex; }
        }

        private void btnPIPBrowseFile_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnPIPUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtPIPFileName.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtPIPFileName.Focus();
                    return;
                }

                string[] s = txtPIPFileName.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "SalePIP" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_PIP }, ref IDFileBatchUpload);

                //PIP Result
                salePIPList.Clear();
                DataTable dtData = NPOIHelper.ImportExceltoDt(txtPIPFileName.Text.Trim(), 0, 0);
                foreach (DataRow dr in dtData.Rows)
                    PackObjectPIP(dr, IDFileBatchUpload.ToString());
                SalePIPBLL salePIPBll = new SalePIPBLL();
                salePIPBll.SavePIP(salePIPList);

                //Load Data
                LoadFileList(Constant.TYPE_PIP);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPIPSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtPIPFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                string status = cbbStatus.Text;
                if (status.Equals("ALL"))
                    status = null;

                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");
                string saleCode = txtSalesCode.Text.Trim();

                SalePIPBLL salePIPBll = new SalePIPBLL();
                string profileId = salePIPBll.GetSaleCodeMapping(saleCode);
                DataTable dtResult = salePIPBll.Search(profileId, status, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvPIPFile_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvFile.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    LoadSaleData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void cbPIPViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtPIPFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPIPAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtPIPFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPIPDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtPIPFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void LoadPIPStatus()
        {
            cbbStatus.Items.Clear();
            cbbStatus.Items.AddRange(new string[] { "ALL", "PIPM-2_F", "PIPM-2_P", "PIPM-1_F", "PIPM-1_P", "PIP", "Non-PIP" });
            cbbStatus.SelectedIndex = 0;
        }

        private void PackObjectPIP(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                    SalePIP salePIP = new SalePIP();
                    salePIP.Month = currentDateOfFile;
                    salePIP.SaleCode = drData["Sales Code"].ToString();
                    salePIP.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    salePIP.Role = drData["Role"].ToString();
                    salePIP.EmployeeFullName = drData["Employee Full Name"].ToString();
                    salePIP.Level = drData["Level"].ToString();
                    salePIP.PIPTarget = drData["PIPTarget"].ToString();
                    salePIP.PIPM1Result = drData["PIPM1Result"].ToString();
                    salePIP.PIPM2Result = drData["PIPM2Result"].ToString();
                    salePIP.CurrentMonthPIPStatus = drData["Current Month PIP Status"].ToString();
                    salePIP.PIPMonth = drData["Month"].ToString();
                    salePIP.BatchFileSaleInfoID = batchFileSaleInfoID;
                    salePIP.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    salePIPList.Add(salePIP);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region SQM REPORT

        private void tabSQM_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(1);
                LoadFileList(Constant.TYPE_SQM);
                LoadSaleData();
                LoadComboBoxField();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSQMBrowse_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnSQMUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtSQMFileName.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtSQMFileName.Focus();
                    return;
                }

                string[] s = txtSQMFileName.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "SaleSQMRawData" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_SQM }, ref IDFileBatchUpload);

                //SQM Result
                saleSQMList.Clear();
                DataTable dtData = NPOIHelper.ImportExceltoDt(txtSQMFileName.Text.Trim(), 1, 0);

                //Filter Row with null data in excel file


                foreach (DataRow dr in dtData.Rows)
                    PackObjectSQMMonthly(dr, IDFileBatchUpload.ToString());
                SaleSQMBLL saleSQMBll = new SaleSQMBLL();
                saleSQMBll.SaveSQM(saleSQMList);

                //Raw Data Result
                saleSQMRawDataList.Clear();
                DataTable dtRawData = NPOIHelper.ImportExceltoDt(txtSQMFileName.Text.Trim(), 0, 0);
                foreach (DataRow dr in dtRawData.Rows)
                    PackObjectSQMRawData(dr, IDFileBatchUpload.ToString());
                SaleSQMRawDataBLL saleSQMRawDataBll = new SaleSQMRawDataBLL();
                saleSQMRawDataBll.SaveSQMRawData(saleSQMRawDataList);

                //Load Data
                LoadFileList(Constant.TYPE_SQM);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSQMSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtSQMFromMonth.Value, dtSQMToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                string fromDate = dtSQMFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtSQMToMonth.Value.ToString("yyyy \\MMM");
                string saleCode = txtSQMSaleCode.Text.Trim();

                SaleSQMBLL saleSQMBll = new SaleSQMBLL();
                string profileId = saleSQMBll.GetSaleCodeMapping(saleCode);
                DataTable dtResult = saleSQMBll.Search(profileId, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvSQMFile_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvSQMFile.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    LoadSaleData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvSQMData_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvSQMData.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    DataRow dr = ((DataRowView)dgvSQMData.SelectedRows[0].DataBoundItem).Row;
                    string SaleCode = dr["Sale Code"].ToString();
                    string batchFileSaleInfo = dr["Batch File Sale Info ID"].ToString();

                    frmDataSQMRawData frmDataSQMRawData = new frmDataSQMRawData(SaleCode, batchFileSaleInfo);
                    frmDataSQMRawData.ShowDialog(this);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSQMAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtSQMToMonth.Value, dtSQMToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };

                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSQMDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtSQMToMonth.Value, dtSQMToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void chbSQMViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtSQMToMonth.Value, dtSQMToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void PackObjectSQMMonthly(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                    SaleSQM saleSQM = new SaleSQM();
                    saleSQM.Month = currentDateOfFile;
                    saleSQM.SaleCode = drData["Sales Code"].ToString();
                    saleSQM.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    saleSQM.Channel = drData["Channel"].ToString();
                    saleSQM.SaleName = drData["Sales Name"].ToString();
                    saleSQM.Role = drData["Role"].ToString();
                    saleSQM.GrandTotalRiskPoint = drData["Grand Total Risk Point"].ToString();
                    saleSQM.IncentiveClawBack = drData["Incentive claw back"].ToString();
                    saleSQM.ComplianceGateKeeper = drData["Compliance gate keeper"].ToString();
                    saleSQM.DisciplinaryImplications = drData["Disciplinary Implications"].ToString();
                    saleSQM.SQMMonth = drData["Month"].ToString();
                    saleSQM.BatchFileSaleInfoID = batchFileSaleInfoID;
                    saleSQM.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    saleSQMList.Add(saleSQM);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectSQMRawData(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code 1"].ToString()))
                {
                    SaleSQMRawData saleSQMRawData = new SaleSQMRawData();
                    saleSQMRawData.Month = currentDateOfFile;
                    saleSQMRawData.ProductType = drData["Products Type"].ToString();
                    saleSQMRawData.ApprovedLimit = drData["Approved Limit"].ToString();
                    saleSQMRawData.Type = drData["TYPE"].ToString();
                    saleSQMRawData.Agent = drData["Agent (Sales Code 1)"].ToString();
                    saleSQMRawData.SaleName = drData["Sales Name"].ToString();
                    saleSQMRawData.Team = drData["Team"].ToString();
                    saleSQMRawData.Role = drData["Role"].ToString();
                    saleSQMRawData.Incidents = drData["INCIDENTS"].ToString();
                    saleSQMRawData.IncidentCode = drData["Incident Code"].ToString();
                    saleSQMRawData.RiskPoint = drData["Risk Point"].ToString();
                    saleSQMRawData.Channel = drData["Channel"].ToString();
                    saleSQMRawData.SourceCode = drData["source_code"].ToString();
                    saleSQMRawData.FullName = drData["fullname"].ToString();
                    saleSQMRawData.SubmitDate = drData["submitdate"].ToString();
                    saleSQMRawData.Product = drData["product"].ToString();
                    saleSQMRawData.Opendate = drData["opendate"].ToString();
                    saleSQMRawData.SQMMonth = drData["Month"].ToString();
                    saleSQMRawData.BatchFileSaleInfoID = batchFileSaleInfoID;

                    saleSQMRawDataList.Add(saleSQMRawData);
                }

            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region DIS REPORT

        private void tabDIS_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(2);
                LoadFileList(Constant.TYPE_DIS);
                LoadSaleData();
                LoadDISStatus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDISBrowse_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnDISUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtFileName.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtDISFileName.Focus();
                    return;
                }

                string[] s = txtDISFileName.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "SaleDIS" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_DIS }, ref IDFileBatchUpload);

                //DIS Result
                saleDISList.Clear();
                DataTable dtData = NPOIHelper.GetDataTableRange("A4:G4", "Sheet1", txtFileName.Text.Trim());
                foreach (DataRow dr in dtData.Rows)
                    PackObjectDIS(dr, IDFileBatchUpload.ToString());
                SaleDISBLL saleDISBll = new SaleDISBLL();
                saleDISBll.SaveDIS(saleDISList);

                //Load Data
                LoadFileList(Constant.TYPE_DIS);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDISSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtDISFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                //set color
                optMode = 0;

                string status = cbbDISStatus.Text;
                if (status.Equals("ALL"))
                    status = null;

                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");
                string saleCode = txtDISSaleCode.Text.Trim();

                SaleDISBLL saleDISBll = new SaleDISBLL();
                string profileId = saleDISBll.GetSaleCodeMapping(saleCode);
                DataTable dtResult = saleDISBll.Search(profileId, status, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvDISFile_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                //set color 
                optMode = 0;
                int selectedRowCount = dgvDISFile.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                    LoadSaleData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDISAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtDISFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDISDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtDISFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void chbDISViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtDISFromMonth.Value, dtPIPToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvDISData_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            try
            {
                foreach (DataGridViewRow r in dgvData.Rows)
                {
                    if (r.Cells["Completion Status"].Value.ToString().Equals("InComplete"))
                    {
                        //DateTime arDate = DateTime.ParseExact(r.Cells["Action Required Date"].Value.ToString(), "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture);
                        DateTime arDate = DateTime.Parse(r.Cells["Action Required Date"].Value.ToString());
                        //Use for notify: Row in reds
                        if (arDate <= DateTime.Now.AddDays(1))
                            r.DefaultCellStyle.BackColor = Color.Red;
                        else
                            r.DefaultCellStyle.BackColor = Color.White;
                        if (appParam.Role.Contains(User.ROLE_APPROVER))
                            r.Cells["Completion Status"] = new DataGridViewLinkCell();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvDISData_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (dgvDISData.Columns[e.ColumnIndex].Name == "Completion Status")
                {
                    string tempStatus = dgvDISData.Rows[e.RowIndex].Cells["Completion Status"].Value.ToString();

                    if (tempStatus.Equals("InComplete") && appParam.Role.Contains(User.ROLE_APPROVER))
                    {
                        string saleCode = dgvDISData.Rows[e.RowIndex].Cells["Sale Code"].Value.ToString();
                        string batchFileSaleInfoID = dgvDISData.Rows[e.RowIndex].Cells["Batch File Sale Info ID"].Value.ToString();

                        DialogResult result = MessageBox.Show("Are you sure to complete?", "Complete confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
                           MessageBoxDefaultButton.Button2);
                        if (result == DialogResult.Yes)
                        {
                            SaleDISBLL saleDISBll = new SaleDISBLL();
                            saleDISBll.UpdateStatus(batchFileSaleInfoID, saleCode, Constant.STATUS_DIS_COMPLETE);

                            dgvDISData.Rows[e.RowIndex].Cells[e.ColumnIndex] = new DataGridViewTextBoxCell();
                            dgvDISData.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = "Complete";

                            //update status main form
                            if (optMode == 1)
                            {
                                DISUpdateStatus(saleDISBll.NotifyDIS().ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDISChangeStatus_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvDISData.DataSource != null)
                {
                    DialogResult result = MessageBox.Show("Are you sure to change all record?", "Change all", MessageBoxButtons.YesNo, MessageBoxIcon.Question,
                          MessageBoxDefaultButton.Button2);
                    if (result == DialogResult.Yes)
                    {
                        DataTable dt = (DataTable)dgvDISData.DataSource;
                        SaleDISBLL saleDISBll = new SaleDISBLL();
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr["Completion Status"].ToString().Equals("InComplete"))
                            {
                                string saleCode = dr["Sale Code"].ToString();
                                string batchFileSaleInfoID = dr["Batch File Sale Info ID"].ToString();
                                saleDISBll.UpdateStatus(batchFileSaleInfoID, saleCode, Constant.STATUS_DIS_COMPLETE);

                                dr["Completion Status"] = "Complete";
                            }
                        }
                        LoadSaleData();
                        MessageBox.Show("Change successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void LoadDISStatus()
        {
            cbbStatus.Items.Clear();
            cbbStatus.Items.AddRange(new string[] {"ALL", 
                                                    "Complete",
                                                    "InComplete",
                                                   });

            cbbStatus.SelectedIndex = 0;
        }

        private void PackObjectDIS(DataRow drData, string batchFileSaleInfoID)
        {

            if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
            {
                //Get PersonalCode and ProfileID from current SaleCode
                string sPersonalCode, sProfileID;
                Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                SaleDIS saleDIS = new SaleDIS();
                saleDIS.Month = currentDateOfFile;
                saleDIS.SaleCode = drData["Sales Code"].ToString();
                saleDIS.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                saleDIS.SaleName = drData["Sales Name"].ToString();
                saleDIS.DisciplinaryAction = drData["Disciplinary Action"].ToString();
                saleDIS.Mistakes = drData["Mistakes"].ToString();
                saleDIS.ReportingDate = string.IsNullOrEmpty(drData["Reporting Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Reporting Date"]);
                saleDIS.ActionRequiredDate = string.IsNullOrEmpty(drData["Action Required Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Action Required Date"]);
                saleDIS.CompletionStatus = drData["Completion Status"].ToString();
                saleDIS.BatchFileSaleInfoID = batchFileSaleInfoID;
                saleDIS.ProfileID = (sProfileID == null) ? "" : sProfileID;
                saleDISList.Add(saleDIS);
            }
        }

        #endregion

        #region PAYMENT REPORT

        private void tabPayment_Click(object sender, EventArgs e)
        {
            LoadDataControlByTab(3);
            LoadFileList(Constant.TYPE_PAYMENT);
            LoadSaleData();
        }

        private void btnPaymentBrowse_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnPaymentUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtPaymentFile.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtPaymentFile.Focus();
                    return;
                }

                string[] s = txtPaymentFile.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "SalePayment" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_PAYMENT }, ref IDFileBatchUpload);

                //Payment Result
                salePaymentList.Clear();
                DataTable dtData = NPOIHelper.ImportExceltoDt(txtPaymentFile.Text.Trim(), 0, 0);
                foreach (DataRow dr in dtData.Rows)
                    PackObjectPayment(dr, IDFileBatchUpload.ToString());
                SalePaymentBLL salePaymentBll = new SalePaymentBLL();
                salePaymentBll.SavePayment(salePaymentList);

                //Load Data
                LoadFileList(Constant.TYPE_PAYMENT);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPaymentSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthPayment.Value, dtiToMonthPayment.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");
                string saleCode = txtSaleCodePayment.Text.Trim();

                SalePaymentBLL salePaymentBll = new SalePaymentBLL();
                string profileId = salePaymentBll.GetSaleCodeMapping(saleCode);
                DataTable dtResult = salePaymentBll.Search(profileId, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvPaymentFile_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvFileNamePayment.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    LoadSaleData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPaymentAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthPayment.Value, dtiToMonthPayment.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnPaymentDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthPayment.Value, dtiToMonthPayment.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void chbPaymentViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthPayment.Value, dtiToMonthPayment.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void PackObjectPayment(DataRow drData, string batchFileSaleInfoID)
        {
            if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
            {
                //Get PersonalCode and ProfileID from current SaleCode
                string sPersonalCode, sProfileID;
                Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                SalePayment salePayment = new SalePayment();
                salePayment.Month = currentDateOfFile;
                salePayment.PayMonth = drData["Pay Month"].ToString();
                salePayment.IncMonth = drData["Inc. Month"].ToString();
                salePayment.SaleCode = drData["Current Sales Code"].ToString();
                salePayment.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                salePayment.EmployeeFullNameE = drData["Employee Full Name (E)"].ToString();
                salePayment.ContractSalary = drData["Contract Salary"].ToString();
                salePayment.BUIPNetScore = drData["BUIP Net Score"].ToString();
                salePayment.PerformanceGrade = drData["Performance Grade"].ToString();
                salePayment.PerfIncentive = drData["Perf. Incentive"].ToString();
                salePayment.DeferralPay = drData["Deferral Pay"].ToString();
                salePayment.SQMDeduction = drData["SQM Deduction"].ToString();
                salePayment.SQMBonus = drData["SQM Bonus"].ToString();
                salePayment.InactiveCardClawback = drData["Inactive Card Clawback"].ToString();
                salePayment.Adjustment = drData["Adjustment"].ToString();
                salePayment.IncentiveAct = drData["Incentive (Act.)"].ToString();
                salePayment.IncentivePaid = drData["Incentive\n(Paid)"].ToString();
                salePayment.ActualWDs = drData["Actual WDs"].ToString();
                salePayment.PaidWDs = drData["Paid WDs"].ToString();
                salePayment.Base = drData["Base"].ToString();
                salePayment.Allowances = drData["Allowances"].ToString();
                salePayment._13thSalaryQuitSettlement = drData["13th Salary/Quit Settlement"].ToString();
                salePayment.NonIncentives = drData["Non-Incentives"].ToString();
                salePayment.SHUIUnionFee = drData["SHUI, Union Fee"].ToString();
                salePayment.Insurance = drData["Insurance"].ToString();
                salePayment.TotalInsurances = drData["∑ Insurances"].ToString();
                salePayment.HRServiceFee = drData["HR Service Fee"].ToString();
                salePayment.GrossEmployeeIncome = drData["Gross Employee Income"].ToString();
                salePayment.PayrollCost = drData["Payroll Cost"].ToString();
                salePayment.Remarks = drData["Remarks"].ToString();
                salePayment.BatchFileSaleInfoID = batchFileSaleInfoID;
                salePayment.ProfileID = (sProfileID == null) ? "" : sProfileID;
                salePaymentList.Add(salePayment);
            }
        }

        #endregion

        #region SALES PRODUCTIVITY REPORT

        private void tabSalesProductivity_Click(object sender, EventArgs e)
        {
            LoadDataControlByTab(4);
            LoadFileList(Constant.TYPE_SALESPRODUCTIVITY);
            tabAgent.PerformClick();
        }

        private void btnBrowseSalesProdtvt_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnSaleProdtvtUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtSalesProdtvtFileName.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtSalesProdtvtFileName.Focus();
                    return;
                }

                string[] s = txtFileName.Text.Trim().Split('\\');

                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "AgentCards", "AgentCardsActivationRate", "AgentPersonalLoan", "AgentLOC", "AgentSuppCard", "AgentFieldSales", "TeamLeaderCards", "TeamLeaderFieldSales", "TeamLeaderPL", "TeamLeaderPortfolio" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_SALESPRODUCTIVITY }, ref IDFileBatchUpload);

                //Clear all data list of Agent and Team Leader
                agentCardsList.Clear();
                agentCardActvtrateList.Clear();
                agentCardActvtrateList.Clear();
                agentPersonalLoanList.Clear();
                agentLOCList.Clear();
                agentSuppCardList.Clear();
                agentFieldSalesList.Clear();
                teamLeaderCardsList.Clear();
                teamLeaderFieldSalesList.Clear();
                teamLeaderPLList.Clear();
                teamLeaderPortfolioList.Clear();

                //Agent -Cards
                DataTable agentCard = NPOIHelper.GetDataTableRange("B3:L3", Constant.AGENT_CARDS, txtFileName.Text.Trim());
                foreach (DataRow dr in agentCard.Rows)
                    PackObjectAgCards(dr, IDFileBatchUpload.ToString());
                AgentCardsBLL agCardBll = new AgentCardsBLL();
                agCardBll.Save(agentCardsList);

                //Agent - AgentCardsActivationRate
                DataTable dtAgCardActRateMOB1 = NPOIHelper.GetDataTableRange("B12:D12", Constant.AGENT_CARDS_ACTIVATION_RATE, txtFileName.Text.Trim());
                foreach (DataRow dr in dtAgCardActRateMOB1.Rows)
                    PackObjectAgCardsActRateMOB1(dr, IDFileBatchUpload.ToString());

                DataTable dtAgCardActRateMOB3 = NPOIHelper.GetDataTableRange("I12:K12", Constant.AGENT_CARDS_ACTIVATION_RATE, txtFileName.Text.Trim());
                foreach (DataRow dr in dtAgCardActRateMOB3.Rows)
                    PackObjectAgCardsActRateMOB3(dr, IDFileBatchUpload.ToString());

                AgentCardsActivationRateBLL agCardActRateBll = new AgentCardsActivationRateBLL();
                agCardActRateBll.Save(agentCardActvtrateList);

                //Agent - Personal Loan
                DataTable dtAgentPerLoan = NPOIHelper.GetDataTableRange("B3:I3", Constant.AGENT_PERSONAL_LOAN, txtFileName.Text.Trim());
                foreach (DataRow dr in dtAgentPerLoan.Rows)
                    PackObjectAgPersonalLoan(dr, IDFileBatchUpload.ToString());
                AgentPersonalLoanBLL agPersonalLoanBLL = new AgentPersonalLoanBLL();
                agPersonalLoanBLL.Save(agentPersonalLoanList);

                //Agent - LOC
                DataTable dtAgentLOC = NPOIHelper.GetDataTableRange("B6:G6", Constant.AGENT_LOC_SUPP_CARD, txtFileName.Text.Trim());
                foreach (DataRow dr in dtAgentLOC.Rows)
                    PackObjectAgLOC(dr, IDFileBatchUpload.ToString());
                AgentLOCBLL agLOCBll = new AgentLOCBLL();
                agLOCBll.Save(agentLOCList);

                //Agent - Supp Card
                DataTable dtAgentSuppCard = NPOIHelper.FindTableRange("B1:G1", Constant.AGENT_LOC_SUPP_CARD, txtFileName.Text.Trim(), "Product", "Suppcard", 3);
                foreach (DataRow dr in dtAgentSuppCard.Rows)
                    PackObjectAgSuppCard(dr, IDFileBatchUpload.ToString());
                AgentSuppCardBLL agSuppCardBll = new AgentSuppCardBLL();
                agSuppCardBll.Save(agentSuppCardList);

                //Agent - Field Sales
                DataTable dtAgentFieldSales = NPOIHelper.GetDataTableRange("A9:H9", Constant.AGENT_FIELD_SALES, txtFileName.Text.Trim());
                foreach (DataRow dr in dtAgentFieldSales.Rows)
                    PackObjectAgFieldSales(dr, IDFileBatchUpload.ToString());
                AgentFieldSalesBLL agFieldSalesBll = new AgentFieldSalesBLL();
                agFieldSalesBll.Save(agentFieldSalesList);

                //Team Leader - Cards
                DataTable dtTeamLeaderCards = NPOIHelper.GetDataTableRange("B7:H7", Constant.TEAMLEADER_CARDS, txtFileName.Text.Trim());
                foreach (DataRow dr in dtTeamLeaderCards.Rows)
                    PackObjectTeamLdCard(dr, IDFileBatchUpload.ToString());
                TeamLeaderCardsBLL tlCardsBll = new TeamLeaderCardsBLL();
                tlCardsBll.Save(teamLeaderCardsList);

                //Team Leader - Field Sales
                DataTable dtTeamLeaderFieldSales = NPOIHelper.GetDataTableRange("B7:I7", Constant.TEAMLEADER_FIELD_SALES, txtFileName.Text.Trim());
                foreach (DataRow dr in dtTeamLeaderFieldSales.Rows)
                    PackObjectTeamLdFieldSale(dr, IDFileBatchUpload.ToString());
                TeamLeaderFieldSalesBLL tlFieldSalesBll = new TeamLeaderFieldSalesBLL();
                tlFieldSalesBll.Save(teamLeaderFieldSalesList);

                //Team Leader - PL
                DataTable dtTeamLeaderPL = NPOIHelper.GetDataTableRange("B7:J7", Constant.TEAMLEADER_PL, txtFileName.Text.Trim());
                foreach (DataRow dr in dtTeamLeaderPL.Rows)
                    PackObjectTeamLdPL(dr, IDFileBatchUpload.ToString());
                TeamLeaderPLBLL tlPLBll = new TeamLeaderPLBLL();
                tlPLBll.Save(teamLeaderPLList);

                //Team Leader - Portfolio
                DataTable dtTeamLeaderPortfolio = NPOIHelper.GetDataTableRange("B7:K7", Constant.TEAMLEADER_PORTFOLIO, txtFileName.Text.Trim());
                foreach (DataRow dr in dtTeamLeaderPortfolio.Rows)
                    PackObjectTeamLdPortfolio(dr, IDFileBatchUpload.ToString());
                TeamLeaderPortfolioBLL tlPortfolioBll = new TeamLeaderPortfolioBLL();
                tlPortfolioBll.Save(teamLeaderPortfolioList);

                //Load List of file to datagridView
                LoadFileList(Constant.TYPE_SALESPRODUCTIVITY);
                if (tabControl2.SelectedTabIndex == 0)
                    tabAgent.PerformClick();
                else
                    tabTeamLeader.PerformClick();

                //When upload completed
                txtFileName.Text = "";
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvFileSaleProdtvt_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvFile.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    if (tabControl2.SelectedTabIndex == 0)
                        tabAgent.PerformClick();
                    else
                        tabTeamLeader.PerformClick();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        #region Sale Productivity Report - Tab Agent

        private void tabAgent_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(5);
                tvAgent.SelectedNode = tvAgent.Nodes[0];
                LoadSaleData();
                tvAgent.Focus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void tvAgent_DoubleClick(object sender, EventArgs e)
        {
            LoadSaleData();
            if (tvAgent.SelectedNode.Text == "Cards Activation Rate")
            {
                labelX28.Visible = true;
                cbbMOBAgent.Visible = true;
            }
            else
            {
                labelX28.Visible = false;
                cbbMOBAgent.Visible = false;
            }
        }

        private void btnAddAgent_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthAgent.Value, dtiToMonthAgent.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDeleteAgent_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthAgent.Value, dtiToMonthAgent.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void cbViewAllAgent_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthAgent.Value, dtiToMonthAgent.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSearchAgent_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthAgent.Value, dtiToMonthAgent.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                cbViewAll.Checked = true;
                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");

                string MOB = string.Empty;
                if (cbbMOB.SelectedItem != null)
                    MOB = cbbMOB.SelectedItem.ToString();

                string saleCode = txtSaleCodeAgent.Text.Trim();
                DataTable dtResult = new DataTable();
                TreeNode selectedNode = tvAgent.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;

                if (string.IsNullOrEmpty(selectedNodeText) || selectedNodeText == "Cards Activation Rate")
                {
                    AgentCardsActivationRateBLL agentCardsActivationRateBLL = new AgentCardsActivationRateBLL();
                    string profileId = agentCardsActivationRateBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentCardsActivationRateBLL.Search(profileId, MOB, fromDate, toDate);
                }
                else if (selectedNodeText == "Cards")
                {
                    AgentCardsBLL agentCardsBLL = new AgentCardsBLL();
                    string profileId = agentCardsBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentCardsBLL.Search(profileId, fromDate, toDate);
                }
                else if (selectedNodeText == "Personal Loan")
                {
                    AgentPersonalLoanBLL agentPersoanlLoanBLL = new AgentPersonalLoanBLL();
                    string profileId = agentPersoanlLoanBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentPersoanlLoanBLL.Search(profileId, fromDate, toDate);
                }
                else if (selectedNodeText == "LOC")
                {
                    AgentLOCBLL agentLOCBLL = new AgentLOCBLL();
                    string profileId = agentLOCBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentLOCBLL.Search(profileId, fromDate, toDate);
                }
                else if (selectedNodeText == "Supp Card")
                {
                    AgentSuppCardBLL agentSuppCardBLL = new AgentSuppCardBLL();
                    string profileId = agentSuppCardBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentSuppCardBLL.Search(profileId, fromDate, toDate);
                }
                else if (selectedNodeText == "Field Sales")
                {
                    AgentFieldSalesBLL agentFieldSalesBLL = new AgentFieldSalesBLL();
                    string profileId = agentFieldSalesBLL.GetSaleCodeMapping(saleCode);
                    dtResult = agentFieldSalesBLL.Search(profileId, fromDate, toDate);
                }
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void PackObjectAgCards(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    AgentCards agentCard = new AgentCards();
                    agentCard.Month = currentDateOfFile;
                    agentCard.SaleAgentMonth = drData["Month"].ToString().Trim();
                    agentCard.SaleCode = drData["Current Sales Code"].ToString();
                    agentCard.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    agentCard.EmployeeName = drData["Employee Name"].ToString();
                    agentCard.Overallnetscore = drData["Overallnetscore"].ToString();
                    agentCard.Classic = drData["Classic"].ToString();
                    agentCard.Gold = drData["Gold"].ToString();
                    agentCard.Platinum = drData["Platinum"].ToString();
                    agentCard.TravelPlatinum = drData["Travel Platinum"].ToString();
                    agentCard.Supplimentary = drData["Supplimentary"].ToString();
                    agentCard.CardApprovalrate = drData["CardApprovalrate"].ToString();
                    agentCard.NTBCards = drData["NTB Cards"].ToString();
                    agentCard.BatchFileSaleInfoID = batchFileSaleInfoID;
                    agentCard.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentCardsList.Add(agentCard);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgCardsActRateMOB1(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    AgentCardsActivationRate saleAgentCardsActivationRate = new AgentCardsActivationRate();
                    saleAgentCardsActivationRate.Month = currentDateOfFile;
                    saleAgentCardsActivationRate.SaleAgentMonth = drData["Month"].ToString().Trim();
                    saleAgentCardsActivationRate.SaleCode = drData["Current Sales Code"].ToString();
                    saleAgentCardsActivationRate.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    saleAgentCardsActivationRate.InactiveCards = drData["Inactive Cards"].ToString();
                    saleAgentCardsActivationRate.MOB = "MOB1";
                    saleAgentCardsActivationRate.BatchFileSaleInfoID = batchFileSaleInfoID;
                    saleAgentCardsActivationRate.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentCardActvtrateList.Add(saleAgentCardsActivationRate);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgCardsActRateMOB3(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    AgentCardsActivationRate saleAgentCardsActivationRate = new AgentCardsActivationRate();
                    saleAgentCardsActivationRate.Month = currentDateOfFile;
                    saleAgentCardsActivationRate.SaleAgentMonth = drData["Month"].ToString().Trim();
                    saleAgentCardsActivationRate.SaleCode = drData["Current Sales Code"].ToString();
                    saleAgentCardsActivationRate.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    saleAgentCardsActivationRate.InactiveCards = drData["Inactive Cards"].ToString();
                    saleAgentCardsActivationRate.MOB = "MOB3";
                    saleAgentCardsActivationRate.BatchFileSaleInfoID = batchFileSaleInfoID;
                    saleAgentCardsActivationRate.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentCardActvtrateList.Add(saleAgentCardsActivationRate);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgPersonalLoan(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["code"].ToString(), out sProfileID);

                    AgentPersonalLoan agentPerLoan = new AgentPersonalLoan();
                    agentPerLoan.Month = currentDateOfFile;
                    agentPerLoan.SaleAgentMonth = drData["Month"].ToString().Trim();
                    agentPerLoan.SaleCode = drData["code"].ToString();
                    agentPerLoan.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    agentPerLoan.NAME = drData["NAME"].ToString();
                    agentPerLoan.Overallnetscore = drData["Overallnetscore"].ToString();
                    agentPerLoan.PLApprovalrate = drData["PLApprovalrate"].ToString();
                    agentPerLoan.Plamount = drData["Plamount"].ToString();
                    agentPerLoan.Plcount = drData["Plcount"].ToString();
                    agentPerLoan.Xsell = drData["X-sell"].ToString();
                    agentPerLoan.BatchFileSaleInfoID = batchFileSaleInfoID;
                    agentPerLoan.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentPersonalLoanList.Add(agentPerLoan);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgLOC(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    AgentLOC agentLOC = new AgentLOC();
                    agentLOC.Month = currentDateOfFile;
                    agentLOC.SaleAgentMonth = drData["Month"].ToString().Trim();
                    agentLOC.SaleCode = drData["Current Sales Code"].ToString();
                    agentLOC.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    agentLOC.NAME = drData["NAME"].ToString();
                    agentLOC.Overallnetscore = drData["Overallnetscore"].ToString();
                    agentLOC.LOCamount = drData["LOCamount"].ToString();
                    agentLOC.Supplimentary = drData["Supplimentary"].ToString();
                    agentLOC.BatchFileSaleInfoID = batchFileSaleInfoID;
                    agentLOC.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentLOCList.Add(agentLOC);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgSuppCard(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {

                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    AgentSuppCard agentSuppCard = new AgentSuppCard();
                    agentSuppCard.Month = currentDateOfFile;
                    agentSuppCard.SaleAgentMonth = drData["Month"].ToString().Trim();
                    agentSuppCard.SaleCode = drData["Current Sales Code"].ToString();
                    agentSuppCard.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    agentSuppCard.NAME = drData["NAME"].ToString();
                    agentSuppCard.Overallnetscore = drData["Overallnetscore"].ToString();
                    agentSuppCard.Supplimentary = drData["Supplimentary"].ToString();
                    agentSuppCard.Supp_approval = drData["Supp_approval"].ToString();
                    agentSuppCard.BatchFileSaleInfoID = batchFileSaleInfoID;
                    agentSuppCard.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentSuppCardList.Add(agentSuppCard);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectAgFieldSales(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["code"].ToString(), out sProfileID);

                    AgentFieldSales agentFieldSales = new AgentFieldSales();
                    agentFieldSales.Month = currentDateOfFile;
                    agentFieldSales.SaleAgentMonth = drData["Month"].ToString().Trim();
                    agentFieldSales.SaleCode = drData["code"].ToString();
                    agentFieldSales.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    agentFieldSales.NAME = drData["NAME"].ToString();
                    agentFieldSales.Overallnetscore = drData["Overallnetscore"].ToString();
                    agentFieldSales.NTB = drData["NTB"].ToString();
                    agentFieldSales.Plcount = drData["Plcount"].ToString();
                    agentFieldSales.LOCSupp = drData["LOC/Supp"].ToString();
                    agentFieldSales.TotalDeals = drData["Total Deals"].ToString();
                    agentFieldSales.BatchFileSaleInfoID = batchFileSaleInfoID;
                    agentFieldSales.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    agentFieldSalesList.Add(agentFieldSales);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region Sale Productivity Report - Tab Team Leader

        private void tabTeamLeader_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(6);
                tvTeamLd.SelectedNode = tvTeamLd.Nodes[0];
                LoadSaleData();
                tvTeamLd.Focus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void tvTeamLd_DoubleClick(object sender, EventArgs e)
        {
            LoadSaleData();
        }

        private void btnAddTeamLd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthTeamLeader.Value, dtiToMonthTeamLeader.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnDeleteTeamLd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthTeamLeader.Value, dtiToMonthTeamLeader.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void cbViewAllTeamLd_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthTeamLeader.Value, dtiToMonthTeamLeader.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnSearchTeamLd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonthTeamLeader.Value, dtiToMonthTeamLeader.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                cbViewAll.Checked = true;
                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");

                string saleCode = txtSaleCodeTeamLd.Text.Trim();
                DataTable dtResult = new DataTable();
                TreeNode selectedNode = tvTeamLd.SelectedNode;
                string selectedNodeText = selectedNode == null ? "" : selectedNode.Text;
                if (string.IsNullOrEmpty(selectedNodeText) || selectedNodeText.Equals("Cards"))
                {
                    TeamLeaderCardsBLL teamLeaderCardsBLL = new TeamLeaderCardsBLL();
                    string personalCode = teamLeaderCardsBLL.GetSaleCodeMapping(saleCode);
                    dtResult = teamLeaderCardsBLL.Search(personalCode, fromDate, toDate);
                }
                else if (selectedNodeText.Equals("Field Sales"))
                {
                    TeamLeaderFieldSalesBLL teamLeaderFieldSalesBLL = new TeamLeaderFieldSalesBLL();
                    string personalCode = teamLeaderFieldSalesBLL.GetSaleCodeMapping(saleCode);
                    dtResult = teamLeaderFieldSalesBLL.Search(personalCode, fromDate, toDate);
                }
                else if (selectedNodeText.Equals("PL"))
                {
                    TeamLeaderPLBLL teamLeaderPLBLL = new TeamLeaderPLBLL();
                    string personalCode = teamLeaderPLBLL.GetSaleCodeMapping(saleCode);
                    dtResult = teamLeaderPLBLL.Search(personalCode, fromDate, toDate);
                }
                else if (selectedNodeText.Equals("Portfolio"))
                {
                    TeamLeaderPortfolioBLL teamLeaderPortfolioBLL = new TeamLeaderPortfolioBLL();
                    string personalCode = teamLeaderPortfolioBLL.GetSaleCodeMapping(saleCode);
                    dtResult = teamLeaderPortfolioBLL.Search(personalCode, fromDate, toDate);
                }
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void PackObjectTeamLdCard(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                    TeamLeaderCards teamLeaderCards = new TeamLeaderCards();
                    teamLeaderCards.Month = currentDateOfFile;
                    teamLeaderCards.SaleCode = drData["Sales Code"].ToString();
                    teamLeaderCards.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    teamLeaderCards.Name = drData["Name"].ToString();
                    teamLeaderCards.Level = drData["Level"].ToString();
                    teamLeaderCards.overallnetscore = drData["overallnetscore"].ToString();
                    teamLeaderCards.AgentonTarget = drData["AgentonTarget"].ToString();
                    teamLeaderCards.NTB = drData["NTB"].ToString();
                    teamLeaderCards.Teamsize = drData["Teamsize"].ToString();
                    teamLeaderCards.BatchFileSaleInfoID = batchFileSaleInfoID;
                    teamLeaderCards.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    teamLeaderCardsList.Add(teamLeaderCards);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectTeamLdFieldSale(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                    TeamLeaderFieldSales teamLeaderFieldSales = new TeamLeaderFieldSales();
                    teamLeaderFieldSales.Month = currentDateOfFile;
                    teamLeaderFieldSales.SaleCode = drData["Sales Code"].ToString();
                    teamLeaderFieldSales.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    teamLeaderFieldSales.EmployeeName = drData["Employee Name"].ToString();
                    teamLeaderFieldSales.Level = drData["Level"].ToString();
                    teamLeaderFieldSales.Category = drData["Category"].ToString();
                    teamLeaderFieldSales.overallnetscore = drData["overallnetscore"].ToString();
                    teamLeaderFieldSales.AgentonTarget = drData["AgentonTarget"].ToString();
                    teamLeaderFieldSales.Teamapproval = drData["Teamapproval"].ToString();
                    teamLeaderFieldSales.Teamsize = drData["Teamsize"].ToString();
                    teamLeaderFieldSales.BatchFileSaleInfoID = batchFileSaleInfoID;
                    teamLeaderFieldSales.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    teamLeaderFieldSalesList.Add(teamLeaderFieldSales);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectTeamLdPL(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    TeamLeaderPL teamLeaderPL = new TeamLeaderPL();
                    teamLeaderPL.Month = currentDateOfFile;
                    teamLeaderPL.SaleCode = drData["Current Sales Code"].ToString();
                    teamLeaderPL.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    teamLeaderPL.EmployeeName = drData["Employee Name"].ToString();
                    teamLeaderPL.Level = drData["Level"].ToString();
                    teamLeaderPL.Category = drData["Category"].ToString();
                    teamLeaderPL.overallnetscore = drData["overallnetscore"].ToString();
                    teamLeaderPL.Plamount = drData["Plamount"].ToString();
                    teamLeaderPL.Plcount = drData["Plcount"].ToString();
                    teamLeaderPL.AgentonTarget = drData["AgentonTarget"].ToString();
                    teamLeaderPL.Teamsize = drData["Teamsize"].ToString();
                    teamLeaderPL.BatchFileSaleInfoID = batchFileSaleInfoID;
                    teamLeaderPL.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    teamLeaderPLList.Add(teamLeaderPL);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void PackObjectTeamLdPortfolio(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    TeamLeaderPortfolio teamLeaderPortfolio = new TeamLeaderPortfolio();
                    teamLeaderPortfolio.Month = currentDateOfFile;
                    teamLeaderPortfolio.SaleCode = drData["Current Sales Code"].ToString();
                    teamLeaderPortfolio.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    teamLeaderPortfolio.EmployeeName = drData["Employee Name"].ToString();
                    teamLeaderPortfolio.Level = drData["Level"].ToString();
                    teamLeaderPortfolio.overallnetscore = drData["overallnetscore"].ToString();
                    teamLeaderPortfolio.LOCamount = drData["LOCamount"].ToString();
                    teamLeaderPortfolio.NTB = drData["NTB"].ToString();
                    teamLeaderPortfolio.Supplimentary = drData["Supplimentary"].ToString();
                    teamLeaderPortfolio.Plamount = drData["Plamount"].ToString();
                    teamLeaderPortfolio.AgentonTarget = drData["AgentonTarget"].ToString();
                    teamLeaderPortfolio.Teamsize = drData["Teamsize"].ToString();
                    teamLeaderPortfolio.BatchFileSaleInfoID = batchFileSaleInfoID;
                    teamLeaderPortfolio.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    teamLeaderPortfolioList.Add(teamLeaderPortfolio);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #endregion

        #region MONTHLY AGENT LIST REPORT

        private void tabMonthlyAgentList_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(7);
                LoadFileList(Constant.TYPE_MONTHLYAGENTLIST);
                LoadSaleData();
                LoadMonthlyAgentListStatus();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnMonthlyAgentListBrowse_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnMonthlyAgentListUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtFileNameMonthlyAgentList.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtFileNameMonthlyAgentList.Focus();
                    return;
                }

                string[] s = txtFileNameMonthlyAgentList.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "MonthlyAgentList" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_MONTHLYAGENTLIST }, ref IDFileBatchUpload);

                //Monthly Agent Result
                monthAgentList.Clear();
                DataTable dtData = NPOIHelper.ImportExceltoDt(txtFileNameMonthlyAgentList.Text.Trim(), 0, 0);
                foreach (DataRow dr in dtData.Rows)
                    PackObjectMonthlyAgentList(dr, IDFileBatchUpload.ToString());
                MonthlyAgentListBLL monthlyAgentListBll = new MonthlyAgentListBLL();
                monthlyAgentListBll.Save(monthAgentList);

                //Load Data
                LoadFileList(Constant.TYPE_MONTHLYAGENTLIST);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvMonthlyAgentListFile_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvFileMonthlyAgentList.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    LoadSaleData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnMonthlyAgentListAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnMonthlyAgentListDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void chbMonthlyAgentListViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnMonthlyAgentListSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                string saleCode = txtSalesCode.Text.Trim();
                string status = cbbStatus.Text;
                if (status.Equals("ALL"))
                    status = null;
                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");

                MonthlyAgentListBLL monthlyAgentListBLL = new MonthlyAgentListBLL();
                string personalCode = monthlyAgentListBLL.GetSaleCodeMapping(saleCode);
                DataTable dtResult = monthlyAgentListBLL.Search(personalCode, status, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void LoadMonthlyAgentListStatus()
        {
            cbbStatusMonthlyAgentList.Items.Clear();
            cbbStatusMonthlyAgentList.Items.AddRange(new string[] { "ALL", "Active", "In Active" });
            cbbStatusMonthlyAgentList.SelectedIndex = 0;
        }

        private void PackObjectMonthlyAgentList(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Current Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Current Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Current Sales Code"].ToString(), out sProfileID);

                    MonthlyAgentList monthlyAgent = new MonthlyAgentList();
                    monthlyAgent.Month = currentDateOfFile;
                    monthlyAgent.SaleCode = drData["Current Sales Code"].ToString();
                    monthlyAgent.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    monthlyAgent.EmployeeFullName = drData["Employee Full Name"].ToString();
                    monthlyAgent.Role = drData["Role"].ToString();
                    monthlyAgent.Level = drData["Level"].ToString();
                    monthlyAgent.Category = drData["Category"].ToString();
                    monthlyAgent.ProductCategory = drData["Product Category"].ToString();
                    monthlyAgent.Product = drData["Product"].ToString();
                    monthlyAgent.TeamName = drData["Team Name"].ToString();
                    monthlyAgent.TeamLeaderCode = drData["Team Leader Code"].ToString();
                    monthlyAgent.Channel = drData["Channel"].ToString();
                    monthlyAgent.JoiningDate = string.IsNullOrEmpty(drData["Joining Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Joining Date"]);
                    monthlyAgent.PromotionRoleTransferDate = string.IsNullOrEmpty(drData["Promotion/Role Transfer Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Promotion/Role Transfer Date"]);
                    monthlyAgent.IDNumber = drData["ID Number"].ToString();
                    monthlyAgent.Status = drData["Status"].ToString();
                    monthlyAgent.ActualLastworkingDate = string.IsNullOrEmpty(drData["Actual Last working Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Actual Last working Date"]);
                    monthlyAgent.ContractTerminationDate = string.IsNullOrEmpty(drData["Contract Termination Date"].ToString()) ? (DateTime?)null : Convert.ToDateTime(drData["Contract Termination Date"]);
                    monthlyAgent.PaidWDs = drData["Paid WDs"].ToString();
                    monthlyAgent.ActualWDs = drData["Actual WDs"].ToString();
                    monthlyAgent.ProgressiveTarget = drData["Progressive Target"].ToString();
                    monthlyAgent.PIP = drData["PIP"].ToString();
                    monthlyAgent.BatchFileSaleInfoID = batchFileSaleInfoID;
                    monthlyAgent.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    monthAgentList.Add(monthlyAgent);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region ATTENDANCE REPORT

        private void tabAttendance_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(9);
                LoadFileList(Constant.TYPE_ATTENDANCE);
                LoadSaleData();
                LoadComboBoxField();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnAttendanceBrowse_Click(object sender, EventArgs e)
        {
            BrowseFile();
        }

        private void btnAttendanceUpload_Click(object sender, EventArgs e)
        {
            try
            {
                if (string.IsNullOrEmpty(txtAttendanceFileName.Text.Trim()))
                {
                    MessageBox.Show("Please input file to upload.", Constant.SYS_ERROR, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtAttendanceFileName.Focus();
                    return;
                }

                string[] s = txtAttendanceFileName.Text.Trim().Split('\\');
                //Get Month of file when Upload
                string strFileName = s[s.Length - 1].Trim();
                if (GetFileMonth(strFileName) == false)
                {
                    MessageBox.Show("Doesn't have Month format at the end of File Name. Please rename it!", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                //Check file size       
                if (checkFileSizeUpload(txtFileName.Text.Trim()) == false)
                    return;

                frmSplash.ShowSplashScreen();
                deleteOldDataBatchFile(new List<string>() { "SaleAttendance" }, s[s.Length - 1]);
                //insert BatchFileSaleInfo DB
                int IDFileBatchUpload = 0;
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                batchFileSaleInfoBll.Save(new BatchFileSaleInfoUpload() { CreatedDate = MiscHelper.DateTimeToShort(DateTime.Now), FileName = s[s.Length - 1], Type = Constant.TYPE_ATTENDANCE }, ref IDFileBatchUpload);

                //SQM Result
                saleAttendanceList.Clear();
                DataTable dtData = NPOIHelper.ImportExceltoDt(txtAttendanceFileName.Text.Trim(), 0, 0);
                foreach (DataRow dr in dtData.Rows)
                    PackObjectAttendance(dr, IDFileBatchUpload.ToString());
                SaleAttendanceBLL saleAttendanceBLL = new SaleAttendanceBLL();
                saleAttendanceBLL.SaveAttendance(saleAttendanceList);

                //Load Data
                LoadFileList(Constant.TYPE_ATTENDANCE);
                LoadSaleData();
                frmSplash.CloseForm();
                MessageBox.Show("Upload Successfully.", Constant.SYS_INFORMATION, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                frmSplash.CloseForm();
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnAttendanceSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                string fromDate = dtiFromMonth.Value.ToString("yyyy \\MMM");
                string toDate = dtiToMonth.Value.ToString("yyyy \\MMM");
                string saleCode = txtAttendanceSaleCode.Text.Trim();

                SaleAttendanceBLL saleAttendanceBLL = new SaleAttendanceBLL();
                string profileId = saleAttendanceBLL.GetSaleCodeMapping(saleCode);
                DataTable dtResult = saleAttendanceBLL.Search(profileId, fromDate, toDate);
                BuildGridViewSaleData(dtResult);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void dgvAttendanceFile_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            try
            {
                int selectedRowCount = dgvAttendanceFile.Rows.GetRowCount(DataGridViewElementStates.Selected);
                if (selectedRowCount > 0)
                {
                    LoadSaleData();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnAttendanceAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };

                ModifyGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void btnAttendanceDelete_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void cbAttendanceViewAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtiFromMonth.Value, dtiToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                CheckBoxCheckedChange();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void PackObjectAttendance(DataRow drData, string batchFileSaleInfoID)
        {
            try
            {
                if (!string.IsNullOrEmpty(drData["Sales Code"].ToString()))
                {
                    //Get PersonalCode and ProfileID from current SaleCode
                    string sPersonalCode, sProfileID;
                    Mapping_SaleCode_PersonalCode.TryGetValue(drData["Sales Code"].ToString(), out sPersonalCode);
                    Mapping_SaleCode_ProfileID.TryGetValue(drData["Sales Code"].ToString(), out sProfileID);

                    SaleAttendance saleAttendance = new SaleAttendance();
                    saleAttendance.Month = currentDateOfFile;
                    saleAttendance.SaleCode = drData["Sales Code"].ToString();
                    saleAttendance.PersonalCode = (sPersonalCode == null) ? "" : sPersonalCode;
                    saleAttendance.ProfileID = (sProfileID == null) ? "" : sProfileID;
                    saleAttendance.SaleName = drData["Name"].ToString();
                    saleAttendance.ActualWorkingDay = drData["Actual Working Day"].ToString();
                    saleAttendance.PaidWDs = drData["Paid WDs"].ToString();
                    saleAttendance.BatchFileSaleInfoID = batchFileSaleInfoID;
                    saleAttendanceList.Add(saleAttendance);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        #endregion

        #region CUSTOM REPORT
        private void tabCustomReport_Click(object sender, EventArgs e)
        {
            try
            {
                LoadDataControlByTab(8);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void BindDataToCustomReportGrid()
        {

            if (selectFieldReport.Count == 0)
            {
                dgvData.DataSource = null;
                dgvData.Columns.Clear();
                return;
            }

            DataTable dtCustomReport = GetCustomReportData();
            dgvData.DataSource = null;
            dgvData.Columns.Clear();
            dgvData.DataSource = dtCustomReport;
            dgvData.AutoResizeColumns();
        }

        private DataTable GetCustomReportData()
        {

            Dictionary<string, string> TablesAndColumnsForQuery = new Dictionary<string, string>();
            Dictionary<string, string> CurrentColumns4Adding = new Dictionary<string, string>();
            List<string> DisplayColumns = new List<string>();
            List<string> cols = new List<string>();
            foreach (var item in selectFieldReport)
            {
                CurrentColumns4Adding.Clear();
                cols.Clear();
                DisplayColumns.Clear();
                foreach (var item2 in selectFieldReport)
                {
                    if (item.Key.Equals(item2.Key))
                    {
                        //TablesAndColumnsForQuery[item.Key] = string.Join(",", item2.Value.Values.ToArray());
                        foreach (var item3 in item2.Value)
                        {
                            cols.Add("[" + item3.Key + "] as [" + item3.Value + "]");
                            CurrentColumns4Adding.Add(item3.Key, item3.Value);
                            DisplayColumns.Add("First([" + item3.Value + "]) as [" + CustomReportTableAndAliasMap[item3.Value.Split('_')[0]] + item3.Value.Split('_')[1] + "]");
                        }
                    }
                    else
                    {
                        foreach (var item4 in item2.Value)
                        {
                            cols.Add("'' as [" + item4.Value + "]");
                            DisplayColumns.Add("First([" + item4.Value + "]) as [" + CustomReportTableAndAliasMap[item4.Value.Split('_')[0]] + item4.Value.Split('_')[1] + "]");
                            //if (item.Value.ContainsKey(item4.Key))
                            //{
                            //    //Same column name but different table
                            //    if (!CurrentColumns4Adding.ContainsKey(item4.Key))
                            //    {
                            //        //Only add if not exist column
                            //        cols.Add(item4.Key);
                            //        CurrentColumns4Adding.Add(item4.Key, item4.Value);
                            //    }
                            //}
                            //else
                            //{
                            //    if (!CurrentColumns4Adding.ContainsKey(item4.Key))
                            //    {
                            //        cols.Add("'' as " + item4.Key);
                            //        CurrentColumns4Adding.Add(item4.Key, item4.Value);
                            //    }
                            //}
                        }
                    }
                }

                TablesAndColumnsForQuery.Add(item.Key, string.Join(",", cols.ToArray()));
            }

            List<string> GroupColumns = new List<string>();
            GroupColumns.Add("[SaleCode]");
            //GroupColumns.Add("[PersonalCode]");
            GroupColumns.Add("[ProfileID]");
            GroupColumns.Add("[Month]");

            List<string> GroupColumnsDisplay = new List<string>();
            GroupColumnsDisplay.Add("[SaleCode] as [Sale Code]");
            //GroupColumnsDisplay.Add("[PersonalCode] as [Personal Code]");
            GroupColumnsDisplay.Add("[ProfileID] as [Profile ID]");
            GroupColumnsDisplay.Add("[Month]");

            CustomReportBLL crbll = new CustomReportBLL();
            DataTable dtCustomReport = crbll.Search(txtCRSaleCode.Text, dtCRFromMonth.Value.ToString("yyyy \\MMM"), dtCRToMonth.Value.ToString("yyyy \\MMM"), dtCRFromMonth.Value.ToString("yyyy\\MMM"), dtCRToMonth.Value.ToString("yyyy\\MMM"), TablesAndColumnsForQuery, DisplayColumns, GroupColumns, GroupColumnsDisplay);
            return dtCustomReport;
        }

        private void btCRSearch_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtCRFromMonth.Value, dtCRToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                BindDataToCustomReportGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void cbCRReport_SelectedIndexChanged(object sender, EventArgs e)
        {

            LoadCustomReportCmbField(cbCRReport.SelectedIndex);
        }

        private void btnCustomAdd_Click(object sender, EventArgs e)
        {
            try
            {
                if (!NPOIHelper.CheckFromDataToDate(dtCRFromMonth.Value, dtCRToMonth.Value))
                {
                    MessageBox.Show("From Month cannot greater than To Month.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                };
                ModifyCustomReportGridViewColumns(1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void ModifyCustomReportGridViewColumns(int op)
        {

            if (cbbField.SelectedIndex >= 0)
            {
                string selectedReportTable = ((DevComponents.Editors.ComboItem)cbCRReport.Items[cbCRReport.SelectedIndex]).Value.ToString();
                if (op == 1)
                {
                    //selectFieldReport4Checking.Add(cbbField.SelectedValue.ToString(), cbbField.SelectedValue.ToString()); //Use to check if field already added
                    if (!selectFieldReport.ContainsKey(selectedReportTable))
                    {
                        selectFieldReport.Add(selectedReportTable, new Dictionary<string, string>());
                        selectFieldReport[selectedReportTable].Add(cbbField.SelectedValue.ToString(), selectedReportTable + "_" + cbbField.Text.ToString());
                    }
                    else
                    {
                        if (!selectFieldReport[selectedReportTable].ContainsKey(cbbField.SelectedValue.ToString()))
                        {
                            selectFieldReport[selectedReportTable].Add(cbbField.SelectedValue.ToString(), selectedReportTable + "_" + cbbField.Text.ToString());
                        }
                    }


                    if (!dgvData.Columns.Contains(cbCRReport.SelectedIndex.ToString() + "_" + cbCRField.SelectedIndex.ToString()))
                    {
                        dgvData.Columns.Add((cbCRReport.SelectedIndex.ToString() + "_" + cbCRField.SelectedIndex.ToString()), cbCRField.Text);
                    }

                    //BindDataToCustomReportGrid();
                }
                else if (op == 2)
                {
                    if (dgvData.Columns.Contains(cbCRReport.SelectedIndex.ToString() + "_" + cbCRField.SelectedIndex.ToString()))
                    {
                        dgvData.Columns.Remove((cbCRReport.SelectedIndex.ToString() + "_" + cbCRField.SelectedIndex.ToString()));
                    }
                    selectFieldReport[selectedReportTable].Remove(cbbField.SelectedValue.ToString());
                    if (selectFieldReport[selectedReportTable].Count == 0)
                    {
                        selectFieldReport.Remove(selectedReportTable);
                    }
                }

                BindDataToCustomReportGrid();
                dgvData.AutoResizeColumns();
            }
            else
            {
                MessageBox.Show("Please choose a column name", "Selection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            btCRSearch.Enabled = (dgvData.Columns.Count > 0);
            buttonX1.Enabled = (dgvData.Columns.Count > 0);
            dgvData.AutoResizeColumns();
        }

        private void btnCustomDel_Click(object sender, EventArgs e)
        {
            try
            {
                ModifyCustomReportGridViewColumns(2);
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        #endregion

        private void ExportDataToExcel(DataTable dt)
        {
            try
            {
                string DefaultFileName = "DefaultFileName";
                string DefaultFileExtension = ".xls";
                string DefaultFileFilterExtension = "Excel 97 - 2003 Workbook (.xls)|*.xls";
                SaveFileDialog saveFile = new SaveFileDialog();
                saveFile.FileName = DefaultFileName;
                saveFile.DefaultExt = DefaultFileExtension;
                saveFile.Filter = DefaultFileFilterExtension;

                // Show save file dialog box
                DialogResult dr = new System.Windows.Forms.DialogResult();
                dr = saveFile.ShowDialog();
                if (dr == System.Windows.Forms.DialogResult.OK)
                {
                    // Save document 
                    string filename = saveFile.FileName;
                    NPOIHelper.ExportDTtoExcel(dt, "", filename);
                }
                else if (dr == System.Windows.Forms.DialogResult.Cancel)
                {
                    return;
                }
            }
            catch (Exception ex) { throw ex; }
        }

        private void buttonX1_Click(object sender, EventArgs e)
        {
            try
            {
                ExportDataToExcel(GetCustomReportData());
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.InnerException == null ? ex.Message : ex.InnerException.Message, Constant.SYS_ERROR, MessageBoxButtons.OK);
                logger.Error(ex.Message + " - " + ex.StackTrace);
            }
        }

        private void deleteOldDataBatchFile(List<string> relatedTables, string FileName)
        {
            try
            {
                BatchFileSaleInfoUploadBLL batchFileSaleInfoBll = new BatchFileSaleInfoUploadBLL();
                DataTable dt = batchFileSaleInfoBll.SearchBatchFileByName(FileName);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    foreach (var item in relatedTables)
                    {
                        batchFileSaleInfoBll.DeleteByBatchFileID(item, dt.Rows[i]["BatchFileSaleInfoID"].ToString());
                    }
                }
            }
            catch (Exception ex) { throw ex; }
        }
    }
}
